I have certain sheets that I want to copy into a workbook I have saved on the C:drive (C:\Book1.xls). I want to do this numerous times so I want to leave Book1.xls as a template for lack of a better word.
I want to know how to code the following:
open book1.xls copy certain worksheets into the book1.xls,
saving book1.xls as another name (based on a variable in the active workbook) to C:\PATS,
closing book1.xls, the original workbook that I copied the sheets from, and how to keep open the new book as well as how to close that too.
thanks in advance
so far i got this:
Dim strNewFolderName As String
Dim rng, rng2 As Range
Dim Msg30, Msg31, style30, title30, response30, response31
Set rng = Sheets("CoDetails").Range("K5")
MyFile2 = rng.Text
strNewFolderName = "PATS" 'define folder name
MkDir ("c:\" & strNewFolderName) 'make the directory
With ThisWorkbook
With .Sheets("CustomSummary")
.Visible = True
' .Unprotect password:= "nipibrmi"
.Copy
.Visible = False
End With
With .Sheets("Finance")
.Visible = True
.Copy After:=Sheets(1)
.Visible = False
End With
With .Sheets("FinanceSummary")
.Visible = True
.Copy After:=Sheets(1)
.Visible = False
End With
end with
Application.Workbooks.Open "C:\Book1.xls"
With Sheets("Custom Summary")
.UsedRange.Copy
.Select
.UsedRange.PasteSpecial Paste:=xlValues
.Visible = True
End With
Application.CutCopyMode = False
For iWS = 2 To Worksheets.Count 'loop for all sheets after summary
With Sheets(iWS)
.Select
.Unprotect
.UsedRange.Copy
.UsedRange.PasteSpecial Paste:=xlValues 'cannot do this on ADMIN sheet
.Protect Password:="nipibrmi"
.Visible = False
Application.CutCopyMode = False 'newly added
End With
Next
ActiveWorkbook.SaveAs filename:="C:\" & strNewFolderName & "\" & MyFile2 'save file[hr]*[/hr] Auto Merged Post;[dl]*[/dl]The reason why I want to do this is because Book1.xls has a password protected VBA project lock, and I figure if I copy sheets into this workbook as compared to a new one, my code on the worksheets I'm copying over into Book1.xls will be protected and not viewable. If that is not the case, please advise.
Thanks