Hi all
I have a problem I hope you can help me with. I have fond some code helping me with copying all the sheets from one workbook into a new one. I am only copying values though. This is done because the workbook should be distributed and i dont want all my formolas in it.
Now I would very much like the page setup for each sheet including header, footer etc. to be copied to the new workbook. Is that possible?
One more thing. I would also like the sheets in the new workbook to have the same name as in the original workbook.
Here is my code until now:
Sub CopySheetsValues()
Dim ThisBookSheets As Long
Dim OldNumSheets As Long
Dim i As Long
Dim ThisWorkbookName As String
OldNumSheets = Application.SheetsInNewWorkbook
ThisBookSheets = ThisWorkbook.Worksheets.Count
ThisWorkbookName = ThisWorkbook.Name
' Add new workbook with as many sheets as are in the current workbook
Application.SheetsInNewWorkbook = ThisBookSheets
Workbooks.Add
For i = 1 To ThisBookSheets
Workbooks(ThisWorkbookName).Sheets(i).Cells.Copy
With Sheets(i).Cells
.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlFormats
End With
Next i
Application.SheetsInNewWorkbook = OldNumSheets
End Sub
I have a problem I hope you can help me with. I have fond some code helping me with copying all the sheets from one workbook into a new one. I am only copying values though. This is done because the workbook should be distributed and i dont want all my formolas in it.
Now I would very much like the page setup for each sheet including header, footer etc. to be copied to the new workbook. Is that possible?
One more thing. I would also like the sheets in the new workbook to have the same name as in the original workbook.
Here is my code until now:
Sub CopySheetsValues()
Dim ThisBookSheets As Long
Dim OldNumSheets As Long
Dim i As Long
Dim ThisWorkbookName As String
OldNumSheets = Application.SheetsInNewWorkbook
ThisBookSheets = ThisWorkbook.Worksheets.Count
ThisWorkbookName = ThisWorkbook.Name
' Add new workbook with as many sheets as are in the current workbook
Application.SheetsInNewWorkbook = ThisBookSheets
Workbooks.Add
For i = 1 To ThisBookSheets
Workbooks(ThisWorkbookName).Sheets(i).Cells.Copy
With Sheets(i).Cells
.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlFormats
End With
Next i
Application.SheetsInNewWorkbook = OldNumSheets
End Sub
Last edited: