Here is the code;
Sub CopySheetNewWorkbook()
Dim CQuestion As String
Dim Response As String
Dim wbName As Variant
Dim wsName As String
Dim strPath As String
Dim vFile As Variant
Dim MyFile As Workbook
'Copy sheet as a new workbook
CQuestion = "Do you want to open another workbook for the year?"
Response = MsgBox(CQuestion, vbQuestion + vbYesNoCancel, "???")
If Response = vbNo Then
Workbooks.Open Filename:="2012.xls", UpdateLinks:=True
Worksheets.Add After:=Worksheets(Worksheets.Count)
wsName = InputBox("Enter name for new worksheet", "New sheet")
If wsName <> "" Then
With ActiveSheet
.Name = wsName
.Visible = True
End With
End If
If wsName = "False" Then Exit Sub 'User Canceled
On Error GoTo GetOut
End If
If Response = vbYes Then
Sheets("Reports").Activate
Sheets("Reports").Copy
wbName = InputBox("Enter current year for linen reports", "New Worbook")
ActiveWorkbook.SaveAs Filename:=wbName
wsName = InputBox("Enter name for new worksheet", "New sheet")
If wsName <> "" Then
With ActiveSheet
.Name = wsName
.Visible = True
End With
End If
End If
If Response = vbCancel Then Exit Sub
If wsName = "False" Then Exit Sub 'User Canceled
ActiveWorkbook.SaveAs Filename:=wbName
'Close the copied and saved workbook
ActiveWorkbook.Close SaveChanges:=False
GetOut:
End Sub
What I am trying to do is instead of having to enter a year into this code (Workbooks.Open Filename:="2012.xls", UpdateLinks:=True) is there anyway to use it right from when the user inputs the date when this line of code is executed? (wbName = InputBox("Enter current year for linen reports", "New Worbook")
Intead of the person managing the code having to go in and change the 2012.xls each year when the user answers yes and input the year in can
it be automatically updated then?
Thanks,
LeapingLizards
Bookmarks