VB help

Please can you help me alter the code in Command button 2 so that instead of copying just sheet4 ("Low Volume") it also copies across sheet6 ("DataSheet")

So basically I want to copy/save the entire workbook as opposed to just the one sheet as is happening at the moment

Command button 2 has been labelled "Archive" on my worksheet

Dim strPath As String, strFileName As String
Dim Extn As String, strFname As String
Dim INUm As Long, CheckMe As Long

strPath = "C:\Documents and Settings\Administrator\My Documents\Gary\project\New Folder\"
strFileName = "MOJ_SummaryData_" & Format(Sheets("Low Volume").Range("C5").Value, "YYYY-MM-DD")
Extn = ".xls"

strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
Do While Len(strFname) <> 0
INUm = INUm + 1
strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
Loop

Sheets("Low Volume").Copy
ActiveWorkbook.SaveAs Filename:=strPath & strFileName & "_" & INUm & Extn
ActiveWorkbook.Close
CheckMe = MsgBox("Clear Contents?", vbYesNo)
If CheckMe = vbYes Then

Sheets("Low Volume").Unprotect Sheets("DataSheet").Range("B2").Value
ThisWorkbook.ActiveSheet.Range("B14:K414,W14:W414,N14:N414,Q14:Q414").ClearContents
Sheets("Low Volume").Protect Sheets("DataSheet").Range("B2").Value
 End If

Open in new window

gary-test3.xls
Gazza83Asked:
Who is Participating?
 
DaveConnect With a Mentor Commented:
Garey,

Just change

Sheets("Low Volume").Copy


to this

Cheers

Dave
Sheets(Array("Low Volume", "DataSheet")).Copy

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.