View Single Post
  #1   Report Post  
DavidB
 
Posts: n/a
Default 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