|
Macro to run on selected sheets
I have a macro that I want to run on 100 of my 103 worksheets, is there a way
this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
Try;
Sub ClearStockCards() Dim ws As Worksheet For Each ws In Worksheets Select Case UCase(ws.Name) Case "SHEET101", "SHEET102", "SHEET103" 'Do nothing Case Else ws.Range("A7:A36,B8:B36,D3").ClearContents End Select Next ws End Sub -- Regards Dave Hawley www.ozgrid.com "terilad" wrote in message ... I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
Hi,
Try this. Change this line S = "Sheet1,Sheet2,Sheet3" To the name of the sheets you DON'T want the code to run on Sub ClearStockCards() Dim Ws As Worksheet S = "Sheet1,Sheet2,Sheet3" V = Split(S, ",") For Each Ws In ThisWorkbook.Worksheets If IsError(Application.Match(Ws.Name, V, 0)) Then Ws.Range("D3,A7:A36,B8:B36").ClearContents End If Next Ws End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "terilad" wrote: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
Hi,
I cant get this code to work it has error run time error cant change part of a merged cell, the merged cells are on the sheets I dont want to run the macro, when I unmerge the cells it runs the macro on the sheets that I dont want it to run on. Any ideas? Many thanks Mark "ozgrid.com" wrote: Try; Sub ClearStockCards() Dim ws As Worksheet For Each ws In Worksheets Select Case UCase(ws.Name) Case "SHEET101", "SHEET102", "SHEET103" 'Do nothing Case Else ws.Range("A7:A36,B8:B36,D3").ClearContents End Select Next ws End Sub -- Regards Dave Hawley www.ozgrid.com "terilad" wrote in message ... I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
If the 100 worksheets are the first 100 ones in the order of the worksheet
tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
They are the last 100
Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
tried editing but not working for me, trying to run macro on sheets I dont
want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
If your code doesn't work, please post it next time!
For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
Hi Stefi,
No Joy. Here's my code. Sub MyMacroToClear() For wi = 5 To 7 Worksheets(wi).Select Call ClearStockCards Next wi End Sub Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Thanks Mark "Stefi" wrote: If your code doesn't work, please post it next time! For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
I cant get this code to work it has error run time error cant change part of
a merged cell, the merged cells are on the sheets I dont want to run the macro, when I unmerge the cells it runs the macro on the sheets that I dont want it to run on. Mark "Stefi" wrote: If your code doesn't work, please post it next time! For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
Which sheets do you want to run the macro on? You wrote that they are the
last 100 while your macro run on sheets 5,6,7 (3 sheets). Which is true? Which cells are merged? Which line causes the error? (Which line is highlighted in yellow while debugging? -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I cant get this code to work it has error run time error cant change part of a merged cell, the merged cells are on the sheets I dont want to run the macro, when I unmerge the cells it runs the macro on the sheets that I dont want it to run on. Mark "Stefi" wrote: If your code doesn't work, please post it next time! For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
Hi Stefi,
I am running the macro on a smaller workbook for testing before placing into the proper one as I dont want data deleted that I have on the full workbook, so I am trying this on 3 sheets on a test workbook. Cells that are merged are A11:F11, but these are merged on worksheets I dont want the macro to run on. The error line is: Range("A7:A36,B8:B36").ClearContents Many thanks for your help. Mark "Stefi" wrote: Which sheets do you want to run the macro on? You wrote that they are the last 100 while your macro run on sheets 5,6,7 (3 sheets). Which is true? Which cells are merged? Which line causes the error? (Which line is highlighted in yellow while debugging? -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I cant get this code to work it has error run time error cant change part of a merged cell, the merged cells are on the sheets I dont want to run the macro, when I unmerge the cells it runs the macro on the sheets that I dont want it to run on. Mark "Stefi" wrote: If your code doesn't work, please post it next time! For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
For testing:
If your tabs are in this order: Sheet1 Sheet2 Sheet3 Sheet4 and merged cells are on Sheet1 then Sub test() For wi = 2 To 4 Worksheets(wi).Select Call ClearStockCards Next wi End Sub Loop For wi = 2 To 4 shall work on Sheet2 Sheet3 Sheet4 The index number of a sheet (in the above example wi) is specified by its position in the tab series and not by the number in sheet name: if your tab series is, e.g. Master Detail1 Detail2 Detail3 then Worksheets(1).name: Master Worksheets(2).name: Detail1 Worksheets(3).name: Detail2 Worksheets(4).name: Detail3 Loop For wi = 2 To 4 shall work on Detail1 Detail2 Detail3 -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Hi Stefi, I am running the macro on a smaller workbook for testing before placing into the proper one as I dont want data deleted that I have on the full workbook, so I am trying this on 3 sheets on a test workbook. Cells that are merged are A11:F11, but these are merged on worksheets I dont want the macro to run on. The error line is: Range("A7:A36,B8:B36").ClearContents Many thanks for your help. Mark "Stefi" wrote: Which sheets do you want to run the macro on? You wrote that they are the last 100 while your macro run on sheets 5,6,7 (3 sheets). Which is true? Which cells are merged? Which line causes the error? (Which line is highlighted in yellow while debugging? -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I cant get this code to work it has error run time error cant change part of a merged cell, the merged cells are on the sheets I dont want to run the macro, when I unmerge the cells it runs the macro on the sheets that I dont want it to run on. Mark "Stefi" wrote: If your code doesn't work, please post it next time! For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
Many thanks, I have noticed that all my sheet numbers are all mixed up, I had
to rename 10 of them in my test, is there a way to do this quick for 103 sheets. Many thanks Mark "Stefi" wrote: For testing: If your tabs are in this order: Sheet1 Sheet2 Sheet3 Sheet4 and merged cells are on Sheet1 then Sub test() For wi = 2 To 4 Worksheets(wi).Select Call ClearStockCards Next wi End Sub Loop For wi = 2 To 4 shall work on Sheet2 Sheet3 Sheet4 The index number of a sheet (in the above example wi) is specified by its position in the tab series and not by the number in sheet name: if your tab series is, e.g. Master Detail1 Detail2 Detail3 then Worksheets(1).name: Master Worksheets(2).name: Detail1 Worksheets(3).name: Detail2 Worksheets(4).name: Detail3 Loop For wi = 2 To 4 shall work on Detail1 Detail2 Detail3 -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Hi Stefi, I am running the macro on a smaller workbook for testing before placing into the proper one as I dont want data deleted that I have on the full workbook, so I am trying this on 3 sheets on a test workbook. Cells that are merged are A11:F11, but these are merged on worksheets I dont want the macro to run on. The error line is: Range("A7:A36,B8:B36").ClearContents Many thanks for your help. Mark "Stefi" wrote: Which sheets do you want to run the macro on? You wrote that they are the last 100 while your macro run on sheets 5,6,7 (3 sheets). Which is true? Which cells are merged? Which line causes the error? (Which line is highlighted in yellow while debugging? -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I cant get this code to work it has error run time error cant change part of a merged cell, the merged cells are on the sheets I dont want to run the macro, when I unmerge the cells it runs the macro on the sheets that I dont want it to run on. Mark "Stefi" wrote: If your code doesn't work, please post it next time! For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
First, it's never a good idea to multipost the same question to different
newsgroups. And it's not a good idea to start a new thread without some indication in the old thread. Option Explicit Sub ReconcileStockCard() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Select Case LCase(wks.Name) 'names of the sheets to skip Case Is = "sheet9", "sheet13", "sheet33" 'do nothing Case Else With wks .Range("a7:a36,b8:b36,d3,d7:d36").value = "" End With End Select Next wks End Sub I changed the range back to what you used in the other thread. Change it if you need to. terilad wrote: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark -- Dave Peterson |
Macro to run on selected sheets
Once again:
Name of the worksheet is neutral in this case, its position number in the series of tabs which is important. if your series of tabs is, e.g. Abrakadabra, Sheet1, Sheet2, then worksheet(1) shall be Abrakadabra, worksheet(2) shall be Sheet1, worksheet(3) shall be Sheet2. But if you change the order of tabs, e.g. like below Sheet1, Sheet2, Abrakadabra then worksheet(1) shall be Sheet1, worksheet(2) shall be Sheet2, worksheet(3) shall be Abrakadabra. You need not to change any sheet name, you only have to group the 3 sheets you dont't want to run the macro on in the first 3 position of your series of tabs, e.g. if names of these 3 sheets are Merged1, Merged2, Merged3, then your series of tabs must look like this: Merged1, Merged2, Merged3, Unmerged1, Unmerged2, ... , Unmerged100 -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Many thanks, I have noticed that all my sheet numbers are all mixed up, I had to rename 10 of them in my test, is there a way to do this quick for 103 sheets. Many thanks Mark "Stefi" wrote: For testing: If your tabs are in this order: Sheet1 Sheet2 Sheet3 Sheet4 and merged cells are on Sheet1 then Sub test() For wi = 2 To 4 Worksheets(wi).Select Call ClearStockCards Next wi End Sub Loop For wi = 2 To 4 shall work on Sheet2 Sheet3 Sheet4 The index number of a sheet (in the above example wi) is specified by its position in the tab series and not by the number in sheet name: if your tab series is, e.g. Master Detail1 Detail2 Detail3 then Worksheets(1).name: Master Worksheets(2).name: Detail1 Worksheets(3).name: Detail2 Worksheets(4).name: Detail3 Loop For wi = 2 To 4 shall work on Detail1 Detail2 Detail3 -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Hi Stefi, I am running the macro on a smaller workbook for testing before placing into the proper one as I dont want data deleted that I have on the full workbook, so I am trying this on 3 sheets on a test workbook. Cells that are merged are A11:F11, but these are merged on worksheets I dont want the macro to run on. The error line is: Range("A7:A36,B8:B36").ClearContents Many thanks for your help. Mark "Stefi" wrote: Which sheets do you want to run the macro on? You wrote that they are the last 100 while your macro run on sheets 5,6,7 (3 sheets). Which is true? Which cells are merged? Which line causes the error? (Which line is highlighted in yellow while debugging? -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I cant get this code to work it has error run time error cant change part of a merged cell, the merged cells are on the sheets I dont want to run the macro, when I unmerge the cells it runs the macro on the sheets that I dont want it to run on. Mark "Stefi" wrote: If your code doesn't work, please post it next time! For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
I only reposted this as I posted the wrong code initialy and the thread was
getting realy confused and difficult to put the issue across, thanks for your input Dave. Regards Mark "Dave Peterson" wrote: First, it's never a good idea to multipost the same question to different newsgroups. And it's not a good idea to start a new thread without some indication in the old thread. Option Explicit Sub ReconcileStockCard() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Select Case LCase(wks.Name) 'names of the sheets to skip Case Is = "sheet9", "sheet13", "sheet33" 'do nothing Case Else With wks .Range("a7:a36,b8:b36,d3,d7:d36").value = "" End With End Select Next wks End Sub I changed the range back to what you used in the other thread. Change it if you need to. terilad wrote: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark -- Dave Peterson . |
Macro to run on selected sheets
Stefi,
I think through time the tabs have been moved around, thus the first 3 sheet tabs which i wish to exclude are named but have the default Sheet4, Sheet7 and Sheet101 in the sheet number in VBA, I renamed these and the code worked, can I rename all sheets quickly with vba or is this not possible? Mark "Stefi" wrote: Once again: Name of the worksheet is neutral in this case, its position number in the series of tabs which is important. if your series of tabs is, e.g. Abrakadabra, Sheet1, Sheet2, then worksheet(1) shall be Abrakadabra, worksheet(2) shall be Sheet1, worksheet(3) shall be Sheet2. But if you change the order of tabs, e.g. like below Sheet1, Sheet2, Abrakadabra then worksheet(1) shall be Sheet1, worksheet(2) shall be Sheet2, worksheet(3) shall be Abrakadabra. You need not to change any sheet name, you only have to group the 3 sheets you dont't want to run the macro on in the first 3 position of your series of tabs, e.g. if names of these 3 sheets are Merged1, Merged2, Merged3, then your series of tabs must look like this: Merged1, Merged2, Merged3, Unmerged1, Unmerged2, ... , Unmerged100 -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Many thanks, I have noticed that all my sheet numbers are all mixed up, I had to rename 10 of them in my test, is there a way to do this quick for 103 sheets. Many thanks Mark "Stefi" wrote: For testing: If your tabs are in this order: Sheet1 Sheet2 Sheet3 Sheet4 and merged cells are on Sheet1 then Sub test() For wi = 2 To 4 Worksheets(wi).Select Call ClearStockCards Next wi End Sub Loop For wi = 2 To 4 shall work on Sheet2 Sheet3 Sheet4 The index number of a sheet (in the above example wi) is specified by its position in the tab series and not by the number in sheet name: if your tab series is, e.g. Master Detail1 Detail2 Detail3 then Worksheets(1).name: Master Worksheets(2).name: Detail1 Worksheets(3).name: Detail2 Worksheets(4).name: Detail3 Loop For wi = 2 To 4 shall work on Detail1 Detail2 Detail3 -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Hi Stefi, I am running the macro on a smaller workbook for testing before placing into the proper one as I dont want data deleted that I have on the full workbook, so I am trying this on 3 sheets on a test workbook. Cells that are merged are A11:F11, but these are merged on worksheets I dont want the macro to run on. The error line is: Range("A7:A36,B8:B36").ClearContents Many thanks for your help. Mark "Stefi" wrote: Which sheets do you want to run the macro on? You wrote that they are the last 100 while your macro run on sheets 5,6,7 (3 sheets). Which is true? Which cells are merged? Which line causes the error? (Which line is highlighted in yellow while debugging? -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I cant get this code to work it has error run time error cant change part of a merged cell, the merged cells are on the sheets I dont want to run the macro, when I unmerge the cells it runs the macro on the sheets that I dont want it to run on. Mark "Stefi" wrote: If your code doesn't work, please post it next time! For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
Macro to run on selected sheets
If your tab order is
Sheet4, Sheet7, Sheet101, other sheets then this sub shall work only on sheets Sheet4, Sheet7, Sheet101 and you don't need to change any sheet names. Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub But if you insist on changing a lot of sheet names via a macro, you can do that if you can specify the old names and the new names assigned to them, either by a rule or a lookup array or range. E.g. if old names are in range("A2:A5") and new names in range("B2:B5") respectively then this sub renames sheets the name of which is found in range("A2:A5") : Sub rentest() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets wsnamerow = "" On Error Resume Next wsnamerow = Range("A1:A5").Find(ws.Name, Range("A1"), xlValues).Row On Error GoTo 0 If wsnamerow < "" Then ws.Name = Range("B" & wsnamerow) Next ws End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Stefi, I think through time the tabs have been moved around, thus the first 3 sheet tabs which i wish to exclude are named but have the default Sheet4, Sheet7 and Sheet101 in the sheet number in VBA, I renamed these and the code worked, can I rename all sheets quickly with vba or is this not possible? Mark "Stefi" wrote: Once again: Name of the worksheet is neutral in this case, its position number in the series of tabs which is important. if your series of tabs is, e.g. Abrakadabra, Sheet1, Sheet2, then worksheet(1) shall be Abrakadabra, worksheet(2) shall be Sheet1, worksheet(3) shall be Sheet2. But if you change the order of tabs, e.g. like below Sheet1, Sheet2, Abrakadabra then worksheet(1) shall be Sheet1, worksheet(2) shall be Sheet2, worksheet(3) shall be Abrakadabra. You need not to change any sheet name, you only have to group the 3 sheets you dont't want to run the macro on in the first 3 position of your series of tabs, e.g. if names of these 3 sheets are Merged1, Merged2, Merged3, then your series of tabs must look like this: Merged1, Merged2, Merged3, Unmerged1, Unmerged2, ... , Unmerged100 -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Many thanks, I have noticed that all my sheet numbers are all mixed up, I had to rename 10 of them in my test, is there a way to do this quick for 103 sheets. Many thanks Mark "Stefi" wrote: For testing: If your tabs are in this order: Sheet1 Sheet2 Sheet3 Sheet4 and merged cells are on Sheet1 then Sub test() For wi = 2 To 4 Worksheets(wi).Select Call ClearStockCards Next wi End Sub Loop For wi = 2 To 4 shall work on Sheet2 Sheet3 Sheet4 The index number of a sheet (in the above example wi) is specified by its position in the tab series and not by the number in sheet name: if your tab series is, e.g. Master Detail1 Detail2 Detail3 then Worksheets(1).name: Master Worksheets(2).name: Detail1 Worksheets(3).name: Detail2 Worksheets(4).name: Detail3 Loop For wi = 2 To 4 shall work on Detail1 Detail2 Detail3 -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Hi Stefi, I am running the macro on a smaller workbook for testing before placing into the proper one as I dont want data deleted that I have on the full workbook, so I am trying this on 3 sheets on a test workbook. Cells that are merged are A11:F11, but these are merged on worksheets I dont want the macro to run on. The error line is: Range("A7:A36,B8:B36").ClearContents Many thanks for your help. Mark "Stefi" wrote: Which sheets do you want to run the macro on? You wrote that they are the last 100 while your macro run on sheets 5,6,7 (3 sheets). Which is true? Which cells are merged? Which line causes the error? (Which line is highlighted in yellow while debugging? -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I cant get this code to work it has error run time error cant change part of a merged cell, the merged cells are on the sheets I dont want to run the macro, when I unmerge the cells it runs the macro on the sheets that I dont want it to run on. Mark "Stefi" wrote: If your code doesn't work, please post it next time! For the last 100 sheets: Sub test() For wi = 4 To 103 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: tried editing but not working for me, trying to run macro on sheets I dont want it to. Mark "Stefi" wrote: If the 100 worksheets are the first 100 ones in the order of the worksheet tabs, then Sub test() For wi = 1 To 100 Worksheets(wi).Select Call ClearStockCards Next wi End Sub -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: I have a macro that I want to run on 100 of my 103 worksheets, is there a way this can be done. My code is: Sub ClearStockCards() Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents End Sub Many thanks Mark |
| All times are GMT +1. The time now is 05:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com