Lee Rabbit
New Member
- Joined
- Apr 30, 2020
- Messages
- 43
- Office Version
- 2010
- Platform
- Windows
Hi Guys,
I have the code below that selects certain worksheets in an array and then saves a new .xlsx file into the users documents folder.
I'd like to expand on this code so that it only copies the active worksheet from the array list. Is this at all possible?
I can't seem to get my head around it.
All the sheets have the same command button that calls the same macro. So all I want to do is call the copy function based on the active sheet I am in.
Any help is always appreciated. Thanks
Lee
I have the code below that selects certain worksheets in an array and then saves a new .xlsx file into the users documents folder.
I'd like to expand on this code so that it only copies the active worksheet from the array list. Is this at all possible?
I can't seem to get my head around it.
All the sheets have the same command button that calls the same macro. So all I want to do is call the copy function based on the active sheet I am in.
Any help is always appreciated. Thanks
Lee
VBA Code:
Sub Save_Week()
Dim ws As Worksheet
Dim strFileName As String
strFileName = InputBox("Please enter a name for the file you are about to create, for example, DSA INSYNC WEEK 1. Once created, the file will be saved in your documents folder on your computer", "YOU ARE ABOUT TO SAVE THIS WEEK'S DATA")
If Trim(strFileName) = vbNullString Then Exit Sub
ActiveWorkbook.Sheets(Array("DSA1 INVOICE", "DCF1 INVOICE", "DBS2 INVOICE", "DBH2 INVOICE", "DPO2 INVOICE", "DSO1 INVOICE", "DSO2 INVOICE")).Copy
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect
ws.UsedRange.Value = ws.UsedRange.Value
ws.Rows(3).Delete
ws.Shapes("CopyRow").Delete
ws.Shapes("SortDrivers").Delete
ws.Shapes("DeleteENTRY").Delete
ws.Shapes("SaveWEEK").Delete
Next ws
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Fldr & strFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub