Hi, I'm hoping you could give me some help. I have zero experience in vba coding but I've managed to kind of mish mash some code together.
After the latest code add it started giving issues. I hope someone on here would be able to help.
I apologise for the nightmares the code might give you.
The purpose of the code is create a new workbook named Daily Capacity YYYY, based on another workbook.
Copy the template named "Master Week" and rename it based on a list on the worksheet named "data", this list can change plus minus a few cells (could potentially hardcode)
Then save the new workbook and close it. I've attached a picture of what it sort should look like the worksheets.
Any help is very appreciated
After the latest code add it started giving issues. I hope someone on here would be able to help.
I apologise for the nightmares the code might give you.
VBA Code:
Private Sub CommandButton1_Click()
Dim FName As String
Dim FPath As String
Dim NewBook As Workbook
Dim WSheet As Worksheet
Dim WsNames As Range, CR As Range
Application.ScreenUpdating = False
' Network drive
FPath = "Z:\Excel"
' Name and Year based on cell value
FName = "Daily Capacity" & Range("A13").Value
' Define the Workbook to copy
Set NewBook = Workbooks.Add(Template:="Z:\Excel\Daily Capacity Master.xlsx")
If Dir(FPath & "\" & FName) <> "" Then
MsgBox "File " & FPath & FName & " already exists"
' If error happens it moves on?
On Error Resume Next
On Error GoTo 0
Else
' Define Worksheet to copy
Set WSheet = Sheets("Master Week")
Set WsNames = Sheets("data").Range("J2:J" & Rows.Count).SpecialCells(2)
For Each CR In WsNames
If Not Evaluate("ISREF('" & CR.Value & " '!J2)") Then
WSheet.Copy After:=Sheets(Sheets.Count)
Active.Sheet.Name = CR.Value
End If
Next CR
WSheet.Select
'Define Save As
With NewBook
' .Title = FName
' add additional code here
NewBook.SaveAs Filename:=FPath & "\" & FName & ".xlsx"
NewBook.Close
'If it doesn't close
'NewBook.Close SaveChanges:=True
End With
Application.ScreenUpdating = True
End Sub
The purpose of the code is create a new workbook named Daily Capacity YYYY, based on another workbook.
Copy the template named "Master Week" and rename it based on a list on the worksheet named "data", this list can change plus minus a few cells (could potentially hardcode)
Then save the new workbook and close it. I've attached a picture of what it sort should look like the worksheets.
Any help is very appreciated