Cells Method Question

[Bonnie]

I'm trying to step through a workbook, sheet by sheet,
copying data in column A to another column each month.
July goes to column F, August to G, etc. I'm using the
cell method to change the column reference each time. My
data has a number month indicator. I get a run time error
telling me that I must have an object for this method but
my Excel VBA reference says that if I don't specify an
object it defaults to the active sheet. Please help.

Here is my code.

'Copy PivotTable results to appropriate month as values.
Do While x <= NumSheetsToPop
Worksheets(x + 1).Activate
Range("A442:A852").Select
Selection.Copy

ActiveSheet.Cells(PasteRow, Month + 5).Select

Selection.PasteSpecialPaste:=xlPasteValues,Operation:=xlNon
, SkipBlanks _
:=False, Transpose:=False
x = x + 1
Loop

 

[J.E. McGimpsey]

I don't get an error using your code, but this will be a bit faster:

Do While x <= NumSheetsToPop
With Worksheets(x + 1)
.Cells(PasteRow, myMonth + 5).Resize(411, 1).Value = _
.Range("A442:A852").Value
End With
x = x + 1
Loop

Note that you almost never need to select or activate a range in
order to work with it. Using the range object directly makes your
code smaller, faster and, IMO, easier to maintain.

Note also that I changed your "Month" variable to "myMonth". In
general it's better not to use variable names that are also reserved
word in VBA - it can get very confusing to maintain.