I am trying to export a workbook into a new one. My up to date code creates a new workbook which will include data from a single worksheet which have been copied and pasted. Although I am struggling to copy a second worksheet into the particular workbook. Any ideas how to deal with this issue ?
Sub Export_Excel() Dim ProjectWB As Workbook Set ProjectWB = ThisWorkBook ' Define source wsheets Dim InputWS As Worksheet Dim CashFlowWS As Worksheet 'defining worksheets Set InputWS = ProjectWB.Sheets("Input") Set CashFlowWS = ProjectWB.Sheets("CF") 'folder's path Dim myPath As String myPath = Application.ActiveWorkbook.Path & "\" 'Create and define export workbook Dim NewProjectWB As Workbook Set NewProjectWB = Workbooks.Add 'Create Broker Rec first sheet Set NewProjectWS = NewProjectWB.Sheets(1) 'copy fist sheet 'NewProjectWS.Name = "Input" 'ProjectWB.Sheets(2).Copy After = NewProjectWB.Sheets(Sheets.Count) 'Copy contents and format of first sheet InputWS.Cells.Copy NewProjectWS.Cells.PasteSpecial Paste:=xlPasteValues NewProjectWS.Cells.PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False NewProjectWS.Activate With ActiveWindow .Zoom = 80 .DisplayGridlines = False .SplitColumn = 4 .SplitRow = 4 .FreezePanes = True End With 'new wb name stPHASE = Range("C2") stBLOCK = Range("C3") stCLUSTER = Range("C4") Dim NewName As String NewName = myPath & stPHASE & "." & stBLOCK & "_" & stCLUSTER & ".xlsx" 'Save workbook with new name ActiveWorkbook.SaveAs Filename:=NewName, FileFormat:=51 'Target File Extension (must include wildcard "*") Dim myExtension As String 'Message Box when tasks are completed MsgBox "Export complete!" End Sub