Need Help!

[SAllen]

I am trying to copy the same specific cells from each sheet in a workbook and copy that to a new workbook. The macro debuger gives me a run-time error '1004' Application-defined or object-defined error stops at the range line. I'm running this from a xls spreadsheet named Cdabakup.xls, could this be a problem? Can anyone help?

Sub Copy()


Dim ws As Worksheet
Dim CellstoCopy() As Variant
Dim x As Integer

For Each ws In ThisWorkbook.Worksheets
ReDim Preserve CellstoCopy(x + 1)
CellstoCopy(x) = ws.[g13]
CellstoCopy(x + 1) = ws.[g32]
x = x + 2
Next ws

Workbooks.Add
Range(Cells(1, 1), Cells(1, x)) = CellstoCopy


End Sub
This message was edited by SAllen on 2002-08-29 18:35

 

[Damon Ostrander]

Hi SAllen,

You code looks fine, and runs fine for me. The only thing I can think of is that this code will not work properly when run from a worksheet's event code module because the Range and Cell references are not qualified (unqualified range references in a worksheet event module refer to the worksheet the code module is contained in, but if this worksheet is not active at the time--as in your case--this presents problems). Are you perhaps calling it from a button Click event within a worksheet's event code module? It should work fine if placed in a standard macro module.