copy COLUMN from 1 worksheet to another (in a different workbook)

I have 2 WORKBOOKS - OLD and NEW

Each Workbook is identical but OLD has OLD data which I need to copy to NEW
workbook a sheet at a time. (about 40 sheets)

I'm trying to copy SELECTED columns from say Oldworkbook sheet 1 to
NewWorkbook Sheet 1 etc..

The following code works fine for Columns A:B for example
but if I do D:D or C:D I get and error!!!

Runtime error: -2147417848 (80010108)
Mehtod 'Copy' of object Range failed

WHY?? - all I've done is changed the column !!

Break shows error at sourceRange.Copy Destination:=destrange
at this point it has copied the 1st sheet

HELP!!!

Code:

Sub CopyColumn()
Dim newbook As Workbook
Dim oldbook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim cnum As Integer
Dim sht As Long
Application.ScreenUpdating = False
Set newbook = ThisWorkbook
Set oldbook = Workbooks.Open("C:\DATA\OLD.XLS")
For skt = 1 To 3 'Sheets.Count - ok limit to 3 sheets for testing
Set sourceRange = oldbook.Worksheets(sht).Columns("D:D")
Set destrange = newbook.Worksheets(sht).Columns("D:D")
sourceRange.Copy Destination:=destrange
Next sht
oldbook.Close
Application.ScreenUpdating = True
End Sub

David,
Are you sure your old workbook has at least 3 worksheets and that each of
them has a column D? Otherwise your code looks fine except for the line For
skt which should be For sht, which I think is only a typographical error.

Swisse

Thanks

Yes, actually the OLD workbook has about 40 worksheets and columns A-V are
populated !!

skt - is a typo !!

"swisse" <[email protected]> wrote in message
news:[email protected]...
> David,
> Are you sure your old workbook has at least 3 worksheets and that each of
> them has a column D? Otherwise your code looks fine except for the line
For
> skt which should be For sht, which I think is only a typographical error.
>
> Swisse
>
>

What happens if you do it manually?

I would have expected a different error (1004) if the "to" worksheet were
protected, though.

Same thing with merged cells.

Any chance that you have other code that's doing more stuff (maybe a worksheet
event?) and interfering with your code (resetting variables when you don't want
variables reset).

DavidB wrote:
>
> I have 2 WORKBOOKS - OLD and NEW
>
> Each Workbook is identical but OLD has OLD data which I need to copy to NEW
> workbook a sheet at a time. (about 40 sheets)
>
> I'm trying to copy SELECTED columns from say Oldworkbook sheet 1 to
> NewWorkbook Sheet 1 etc..
>
> The following code works fine for Columns A:B for example
> but if I do D:D or C:D I get and error!!!
>
> Runtime error: -2147417848 (80010108)
> Mehtod 'Copy' of object Range failed
>
> WHY?? - all I've done is changed the column !!
>
> Break shows error at sourceRange.Copy Destination:=destrange
> at this point it has copied the 1st sheet
>
> HELP!!!
>
> Code:
>
> Sub CopyColumn()
> Dim newbook As Workbook
> Dim oldbook As Workbook
> Dim sourceRange As Range
> Dim destrange As Range
> Dim cnum As Integer
> Dim sht As Long
> Application.ScreenUpdating = False
> Set newbook = ThisWorkbook
> Set oldbook = Workbooks.Open("C:\DATA\OLD.XLS")
> For skt = 1 To 3 'Sheets.Count - ok limit to 3 sheets for testing
> Set sourceRange = oldbook.Worksheets(sht).Columns("D:D")
> Set destrange = newbook.Worksheets(sht).Columns("D:D")
> sourceRange.Copy Destination:=destrange
> Next sht
> oldbook.Close
> Application.ScreenUpdating = True
> End Sub

--

Dave Peterson