Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro over a number of sheets
I have this small macro that i recorded that does a few small functions
including copying & pasting a small bit of info from one sheet onto another sheet. The thing is that I have to do this to about 50 sheets. Is there a way to have the macro jump to the next sheet and do the same thing automatically? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro over a number of sheets
Put this code around your macro code: For Each Sheet In Worksheets Sheet.Activate ' Your code here Next Sheet If you want it to miss out the first sheet, use this: For Each Sheet In Worksheets if Sheet.Name < "Sheet1" Then Sheet.Activate ' Your code Endif Next Sheet The name to check for is the name on the sheet tab. HTH Helen -----Original Message----- I have this small macro that i recorded that does a few small functions including copying & pasting a small bit of info from one sheet onto another sheet. The thing is that I have to do this to about 50 sheets. Is there a way to have the macro jump to the next sheet and do the same thing automatically? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro over a number of sheets
Hi
try something like dim wks as worksheet for each wks in worksheets 'your code next -- Regards Frank Kabel Frankfurt, Germany "Dominique Feteau" schrieb im Newsbeitrag ... I have this small macro that i recorded that does a few small functions including copying & pasting a small bit of info from one sheet onto another sheet. The thing is that I have to do this to about 50 sheets. Is there a way to have the macro jump to the next sheet and do the same thing automatically? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro over a number of sheets
for a better answer, you need to post your code with a clearer explanation
of what you want to achieve. You imply that the code is moving between sheets now, using either of the solutions offered may work or they may not depending on how your macro determines where to paste the information. It sounds like you might want some type of consolidation macro, in which case, just wrapping a loop around your existing code might not be the whole solution. -- Regards, Tom Ogilvy "Dominique Feteau" wrote in message ... I have this small macro that i recorded that does a few small functions including copying & pasting a small bit of info from one sheet onto another sheet. The thing is that I have to do this to about 50 sheets. Is there a way to have the macro jump to the next sheet and do the same thing automatically? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro over a number of sheets (almost)
Thanks Helen
That works but maybe i should give u a little bit more info on my code. What its doing is copying some info from one sheet in one workbook, going to another open workbook, inserting a new worksheet, a message box opens so i can input the name of that new worksheet, and finally pasting that copied info onto the new sheet. I tried what you told me, but it wasnt changing the sheet. Heres the code: Sub Access() Dim RenamSheet As String 'here is where it copies assuming that workbook and sheet i have copied is selected Range("B26:M28").Select Selection.Copy Windows("Access.xls").Activate 'add the new sheet and rename it Sheets.Add RenamSheet = InputBox("Rename Sheet") ActiveSheet.Name = RenamSheet Range("C1").Select 'here is where it pastes that new info along with some other formatting Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("B1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "January" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault Range("B1:B12").Select Range("A1").Select ActiveCell.FormulaR1C1 = "Annual Subscription Fees" Range("A2").Select Columns("A:A").EntireColumn.AutoFit Range("A1").Select Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault Range("A1:A12").Select Range("A13").Select ActiveCell.FormulaR1C1 = "Consultative Support" Range("A13").Select Selection.AutoFill Destination:=Range("A13:A24"), Type:=xlFillDefault Range("A13:A24").Select Range("A25").Select ActiveCell.FormulaR1C1 = "Production" Range("A25").Select Selection.AutoFill Destination:=Range("A25:A36"), Type:=xlFillDefault Range("A25:A36").Select Range("B1:B12").Select Selection.Copy Range("B13").Select ActiveSheet.Paste Range("B25").Select ActiveSheet.Paste Range("D1:D12").Select Application.CutCopyMode = False Selection.Cut Range("C13").Select ActiveSheet.Paste Range("E1:E12").Select Selection.Cut Range("C25").Select ActiveSheet.Paste Range("A1").Select 'then goes back to the original file Windows("Activebillings2004.xls").Activate End Sub "Helen Trim" wrote in message ... Put this code around your macro code: For Each Sheet In Worksheets Sheet.Activate ' Your code here Next Sheet If you want it to miss out the first sheet, use this: For Each Sheet In Worksheets if Sheet.Name < "Sheet1" Then Sheet.Activate ' Your code Endif Next Sheet The name to check for is the name on the sheet tab. HTH Helen -----Original Message----- I have this small macro that i recorded that does a few small functions including copying & pasting a small bit of info from one sheet onto another sheet. The thing is that I have to do this to about 50 sheets. Is there a way to have the macro jump to the next sheet and do the same thing automatically? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro over a number of sheets
i just posted my code in the response to helen trim's solution.
thanks tom "Tom Ogilvy" wrote in message ... for a better answer, you need to post your code with a clearer explanation of what you want to achieve. You imply that the code is moving between sheets now, using either of the solutions offered may work or they may not depending on how your macro determines where to paste the information. It sounds like you might want some type of consolidation macro, in which case, just wrapping a loop around your existing code might not be the whole solution. -- Regards, Tom Ogilvy "Dominique Feteau" wrote in message ... I have this small macro that i recorded that does a few small functions including copying & pasting a small bit of info from one sheet onto another sheet. The thing is that I have to do this to about 50 sheets. Is there a way to have the macro jump to the next sheet and do the same thing automatically? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro over a number of sheets
I'll wait quitely and see what Helen responds with.
-- Regards, Tom Ogilvy "Dominique Feteau" wrote in message ... i just posted my code in the response to helen trim's solution. thanks tom "Tom Ogilvy" wrote in message ... for a better answer, you need to post your code with a clearer explanation of what you want to achieve. You imply that the code is moving between sheets now, using either of the solutions offered may work or they may not depending on how your macro determines where to paste the information. It sounds like you might want some type of consolidation macro, in which case, just wrapping a loop around your existing code might not be the whole solution. -- Regards, Tom Ogilvy "Dominique Feteau" wrote in message ... I have this small macro that i recorded that does a few small functions including copying & pasting a small bit of info from one sheet onto another sheet. The thing is that I have to do this to about 50 sheets. Is there a way to have the macro jump to the next sheet and do the same thing automatically? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro over a number of sheets
Hey Tom
You think u can help me out? Niq "Tom Ogilvy" wrote in message ... I'll wait quitely and see what Helen responds with. -- Regards, Tom Ogilvy "Dominique Feteau" wrote in message ... i just posted my code in the response to helen trim's solution. thanks tom "Tom Ogilvy" wrote in message ... for a better answer, you need to post your code with a clearer explanation of what you want to achieve. You imply that the code is moving between sheets now, using either of the solutions offered may work or they may not depending on how your macro determines where to paste the information. It sounds like you might want some type of consolidation macro, in which case, just wrapping a loop around your existing code might not be the whole solution. -- Regards, Tom Ogilvy "Dominique Feteau" wrote in message ... I have this small macro that i recorded that does a few small functions including copying & pasting a small bit of info from one sheet onto another sheet. The thing is that I have to do this to about 50 sheets. Is there a way to have the macro jump to the next sheet and do the same thing automatically? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count number of sheets | Excel Worksheet Functions | |||
Count number of sheets | Excel Discussion (Misc queries) | |||
Name and number of sheets | Excel Discussion (Misc queries) | |||
Loop across Sheets and number of sheets | Excel Programming | |||
Max Number of Sheets | Excel Programming |