Hi,
I currently have several files that are macro enabled and have different vba modules that need to be copied to an xlsx file every quarter. I have found code to accomplish this but the problem I'm having is this does not copy the print formats and page setup format from the original file. Could someone please help me with this?
Below is the code that I am currently using.
Thanks so much
Sub workbookcopy()
Dim wbCopy As Workbook
Dim wbPaste As Workbook
Set wbCopy = ThisWorkbook
Set wbPaste = Workbooks.Add
Dim stCopy As Worksheet
Dim stPaste As Worksheet
For Each stCopy In wbCopy.Sheets
If stCopy.Visible = True Then
Set stPaste = wbPaste.Sheets.Add
stPaste.Name = stCopy.Name
stCopy.UsedRange.Copy
stPaste.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
stPaste.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
stPaste.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End If
Next
End Sub
Bookmarks