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" wrote in message
...
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