Lee Rabbit
New Member
- Joined
- Apr 30, 2020
- Messages
- 43
- Office Version
- 2010
- Platform
- Windows
Hi,
I have the below code to copy sheet values into a new workbook.
I am trying to include a couple of sheets from external workbooks that are located in the same folder as the original.
Workbooks are named "EXPORT 1" sheet name "TEST 1" and "EXPORT 2" sheet name "TEST 2".
Is there a way to merge this action into the existing code?
Thanks in advance.
Lee
I have the below code to copy sheet values into a new workbook.
I am trying to include a couple of sheets from external workbooks that are located in the same folder as the original.
Workbooks are named "EXPORT 1" sheet name "TEST 1" and "EXPORT 2" sheet name "TEST 2".
Is there a way to merge this action into the existing code?
Thanks in advance.
Lee
VBA Code:
Sub Duplicate_Workbook()
Dim ws As Worksheet
Dim strFileName As String
strFileName = InputBox("Type a name for the new workbook", "File Name")
If Trim(strFileName) = vbNullString Then Exit Sub
ActiveWorkbook.Sheets(Array("INVOICE 1", "INVOICE 2", "INVOICE 3", "INVOICE 4", "INVOICE 5", "INVOICE 6", "INVOICE 7")).Copy
For Each ws In ActiveWorkbook.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
ws.Rows(3).Delete
ws.Shapes("CopyRow").Delete
ws.Shapes("SortDrivers").Delete
ws.Shapes("DeleteENTRY").Delete
Next ws
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\PDF\" & strFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub