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 |
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 |
All times are GMT +1. The time now is 07:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com