Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells Method Question
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,Operati on:=xlNon , SkipBlanks _ :=False, Transpose:=False x = x + 1 Loop |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells Method Question
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. In article , "Bonnie" wrote: 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,Operati on:=xlNon , SkipBlanks _ :=False, Transpose:=False x = x + 1 Loop |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Method for protecting cells which still allows quick editing | Excel Discussion (Misc queries) | |||
Please post this thread a correct full method, method about | New Users to Excel | |||
Please post this thread a complete correct method, method about te | New Users to Excel | |||
Question on RATE()-like method for non-straightline values | Excel Discussion (Misc queries) |