Copy Cell Routine (FAO Tom Ogilvie)

[Gazza]

Tom,

The code you kindly supplied to me from an earlier post generates a Run-Time
Error (424) "Object Required"

It stops at the line: Set rng = .Range(.Cells(1, 1), .Cells(1,
1).End(xlDown)).Value


Am I doing something wrong??

Thanks
Gazza

Option Explicit
Option Base 0
'
'

'specify your Source & Destination workbooks & worksheets in this
'section
' the number of cells to copy -1
' the source & destination cells in the 2 arrays

'Source workbook & sheet
Const SOURCE_Sheet = "Sheet1"
Const SOURCE_Workbook = "Book1.xls"

'Destination workbook & sheet
Const DEST_Sheet = "Sheet2"
Const DEST_Workbook = "C:\Shared Documents/Book2.xls"
Const SAVE_book = "Book2.xls"



Sub Copy()

Dim DataSource 'cell locations of data to move
Dim DataDest 'cell destinations
Dim Data() 'array holding value of data in cells
Dim element As Integer 'array element pointer
Dim rng As Range 'cells holding source list
Dim CopyCells As Long 'no of cells to copy

Application.ScreenUpdating = False

With Worksheets("CellList")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Value
End With

CopyCells = rng.Rows.Count

ReDim Data(1 To CopyCells)

'location of cells to copy
DataSource = rng.Value

'location of cells to copy into
DataDest = rng.Offset(0, 1).Value

'read data into array
For element = 1 To CopyCells
Data(element) = Worksheets(SOURCE_Sheet).Range(DataSource(element,
1))
Next element

'Open Destination Workbook at correct sheet
'Application.ShowWindowsInTaskbar = False
Workbooks.Open Filename:=DEST_Workbook
Worksheets(DEST_Sheet).Select


'copy data into Destination worksheet
For element = 0 To CopyCells
Worksheets(DEST_Sheet).Range(DataDest(element, 1)) = Data(element)
Next element

'return to Source book
Windows(SOURCE_Workbook).Activate
Workbooks(SAVE_book).Close savechanges:=True

End Sub

 

[Tom Ogilvy]

With Worksheets("CellList")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Value
End With

shouldn't have .Value on the end

With Worksheets("CellList")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With

--
Regards,
Tom Ogilvy


ws:%[email protected]...

 

[Gazza]

Tom,

WOW - fast reply have made that change but now get a runtime error (9)
Subscript out of range

STOPPING at line; Worksheets(DEST_Sheet).Range(DataDest(element, 1)) =
Data(element)

(Nearly there I hope)

Thanks agian
Gazza

 

[Tom Ogilvy]

What is the value of the Constant DEST_Sheet. Is that a valid worksheet
name in the activeworkbook?

That seems the most likely cause to me.