Write up only 1 VBA

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
Hi
I have below VBA which I HAVE TO WRITE UP 50 TIMES because i have 50 worksheets.
Is there a way to only write up this VBA once but still use the 50 worksheets?
The contents to be cleared in the active sheet is always in the range C6:E6
However in sheet1 the clear contents range changes because the data is on a different row depending which worksheet is open.
The clear contents range in Sheet 1 can be B3:D3 OR IT can be B4:D4 OR IT can be B5:D5 etc
Also depending which worksheet is open the range can be A3 or A4 or A5 etc in Sheet 1

I hope I have explained what I'm after


Sub SaveSheetToPDF5()
Dim ws As Worksheet
Set ws = ActiveSheet
Sheet1.Unprotect Password:="Zebra007"
ws.Unprotect Password:="Zebra007"
ws.Range("A1:I12").ExportAsFixedFormat xlTypePDF, Filename:= _
"C:\Users\AClass\Desktop\Parcels Collected\" & ws.Range("A3").Value & ws.Range("B3").Value & ws.Range("C3").Value, Openafterpublish:=False
Sheet1.Range("B3:D3").ClearContents
ws.Range("C6:E6").ClearContents
ws.Protect Password:="Zebra007", UserInterfaceOnly:=True
Sheet1.Range("A3").Value = Sheet7.Range("A3").Value Mod 99 + 1.01
Sheet1.Protect Password:="Zebra007", UserInterfaceOnly:=True
Sheet1.Activate
End Sub
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,717
To apply it to ALL sheets use:

VBA Code:
Sub WorksheetLoop()

Dim ws As Worksheet

For Each ws In Sheets
   'your code here
Next ws

End Sub



For MOST Sheets, add an IF

VBA Code:
Sub WorksheetLoop()

Dim ws As Worksheet

For Each ws In Sheets
If ws.name <> "This" and ws.name <> "That" then
   'your code here
End If
Next ws

End Sub


To apply some code to all use:

VBA Code:
Sub WorksheetLoop()

Dim ws As Worksheet

  'some code here runs once

For Each ws In Sheets
   'rest of the code here for each sheet
Next ws

End Sub
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,709
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
1) Use Code Tags when posting code.Select the portion of your post that is the code and click on the "</>" up on top.
2) The contents to be cleared in the active sheet is always in the range C6:E6
"In the range" means that it could be C6, D6 or E6 or any combination. How to figure out which range?
Or do you mean that it always is Range("C6:E6")
3) The clear contents range in Sheet 1 can be B3:D3 OR IT can be B4:D4 OR IT can be B5:D5 etc
What makes it be these different ranges?
4) Also depending which worksheet is open the range can be A3 or A4 or A5 etc in Sheet 1
What does that mean?
In your code, A3 value is part of your "save as" name so I don't think that you mean to delete it.
5) You forgot to put & ".PDF" as the last part of your save as pdf file name.

 

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
To apply it to ALL sheets use:

VBA Code:
Sub WorksheetLoop()

Dim ws As Worksheet

For Each ws In Sheets
   'your code here
Next ws

End Sub



For MOST Sheets, add an IF

VBA Code:
Sub WorksheetLoop()

Dim ws As Worksheet

For Each ws In Sheets
If ws.name <> "This" and ws.name <> "That" then
   'your code here
End If
Next ws

End Sub


To apply some code to all use:

VBA Code:
Sub WorksheetLoop()

Dim ws As Worksheet

  'some code here runs once

For Each ws In Sheets
   'rest of the code here for each sheet
Next ws

End Sub
Hi
Sheet 1 has 50 rows. Each row can be linked to a different worksheet, a total of 50 worksheets. The data in row 1 on sheet 1 is copied to sheet 2. Likewise the data in row 2 on sheet 1 is copied to sheet 3 and so forth for the other rows in sheet 1. The idea is that when i link to sheet 2 from row 1 on sheet 1, I then save sheet 2 to file and clear contents I don't need on sheet 2 and clear the contents of row 1 on sheet 1. Similarly if I have to link to Sheet 3, 4 etc. As you can see the contents that have to be cleared on sheets 2 through to 50 remain the same because they are in the same location . However the location of data on each row in sheet 1 changes depending which sheet 2 through to 50 is chosen. This is why i have written a vba code for each sheet from sheet 2 to 50. i was hoping to only write 1 vba code to achieve what i want to do instead of writing a separate code for each sheet 2 through to 50
 

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
1) Use Code Tags when posting code.Select the portion of your post that is the code and click on the "</>" up on top.
2) The contents to be cleared in the active sheet is always in the range C6:E6
"In the range" means that it could be C6, D6 or E6 or any combination. How to figure out which range?
Or do you mean that it always is Range("C6:E6")
3) The clear contents range in Sheet 1 can be B3:D3 OR IT can be B4:D4 OR IT can be B5:D5 etc
What makes it be these different ranges?
4) Also depending which worksheet is open the range can be A3 or A4 or A5 etc in Sheet 1
What does that mean?
In your code, A3 value is part of your "save as" name so I don't think that you mean to delete it.
5) You forgot to put & ".PDF" as the last part of your save as pdf file name.

Hi
In response
1) Apologies will do next time
2) Always Range("C6:E6")
3) Different ranges depending which row is selected on sheet 1
4) Again, dependent which row is selected
Whether it is A3, A4 etc, these values are never deleted
5) I am asking for my file to be saved as a PDF file. I have had no problems with file being saved as PDF.

Obviously I have caused confusion. Perhaps what I have written below explains what I am trying to achieve

Sheet 1 has 50 rows. Each row can be linked to a different worksheet, a total of 50 worksheets. The data in row 1 on sheet 1 is copied to sheet 2. Likewise the data in row 2 on sheet 1 is copied to sheet 3 and so forth for the other rows in sheet 1. The idea is that when i link to sheet 2 from row 1 on sheet 1, I then save sheet 2 to file and clear contents I don't need on sheet 2 and clear the contents of row 1 on sheet 1. Similarly if I have to link to Sheet 3, 4 etc. As you can see the contents that have to be cleared on sheets 2 through to 50 remain the same because they are in the same location . However the location of data on each row in sheet 1 changes depending which sheet 2 through to 50 is chosen. This is why i have written a vba code for each sheet from sheet 2 to 50. i was hoping to only write 1 vba code to achieve what i want to do instead of writing a separate code for each sheet 2 through to 50
 

Watch MrExcel Video

Forum statistics

Threads
1,127,679
Messages
5,626,237
Members
416,168
Latest member
DROP_DATABASE_MrExel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top