Tonysdilemma
New Member
- Joined
- Aug 2, 2020
- Messages
- 30
- Office Version
- 2016
- Platform
- 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
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