Snasui Excel Forum

Code: Select all

Private Sub CommandButton2_Click()

Const TARGETWORKBOOK  As String = "OUTPUTFILE.xlsx"
Dim wbkTarget           As Workbook
Set wbkTarget = Workbooks.Open(FileName:=ThisWorkbook.Path & "\" & TARGETWORKBOOK)


Dim wsCopy1 As Worksheet 'INPUTFILE
Dim wsCopy2 As Worksheet 'INPUTFILE
Dim wsCopy3 As Worksheet 'INPUTFILE

Dim wsDest1 As Worksheet 'OUTPUTFILE
Dim wsDest2 As Worksheet 'OUTPUTFILE
Dim wsDest3 As Worksheet 'OUTPUTFILE

  Set wsCopy1 = Workbooks("INPUTFILE.xlsm").Worksheets("T1")
  Set wsCopy2 = Workbooks("INPUTFILE.xlsm").Worksheets("T2")
  Set wsCopy3 = Workbooks("INPUTFILE.xlsm").Worksheets("T3")


  Set wsDest1 = Workbooks("OUTPUTFILE.xlsx").Worksheets("T1")
  Set wsDest2 = Workbooks("OUTPUTFILE.xlsx").Worksheets("T2")
  Set wsDest3 = Workbooks("OUTPUTFILE.xlsx").Worksheets("T3")
  
  wsCopy1.Range("A:Z").Copy
  wsDest1.Range("A:Z").PasteSpecial
  wsCopy2.Range("A:Z").Copy
  wsDest2.Range("A:Z").PasteSpecial
  wsCopy3.Range("A:Z").Copy
  wsDest3.Range("A:Z").PasteSpecial
  
  Workbooks("INPUTFILE.xlsx").Save
  Workbooks("OUTPUTFILE.xlsm").Save
  

End Sub

Code: Select all

Private Sub CommandButton2_Click()
Dim wbkTarget As New Workbook
Set wbkTarget = Workbooks.Open(fileName:=ThisWorkbook.Path & "\OUTPUTFILE.xlsx")

Dim wsCopy1 As Worksheet 'INPUTFILE
Dim wsCopy2 As Worksheet 'INPUTFILE
Dim wsCopy3 As Worksheet 'INPUTFILE

Dim wsDest1 As Worksheet 'OUTPUTFILE
Dim wsDest2 As Worksheet 'OUTPUTFILE
Dim wsDest3 As Worksheet 'OUTPUTFILE

  Set wsCopy1 = Workbooks("INPUTFILE.xlsm").Worksheets("T1")
  Set wsCopy2 = Workbooks("INPUTFILE.xlsm").Worksheets("T2")
  Set wsCopy3 = Workbooks("INPUTFILE.xlsm").Worksheets("T3")


  Set wsDest1 = Workbooks("OUTPUTFILE.xlsx").Worksheets("T1")
  Set wsDest2 = Workbooks("OUTPUTFILE.xlsx").Worksheets("T2")
  Set wsDest3 = Workbooks("OUTPUTFILE.xlsx").Worksheets("T3")
  
  wsCopy1.Range("A:Z").Copy
  wsDest1.Range("A:Z").PasteSpecial
  wsCopy2.Range("A:Z").Copy
  wsDest2.Range("A:Z").PasteSpecial
  wsCopy3.Range("A:Z").Copy
  wsDest3.Range("A:Z").PasteSpecial
  
  ActiveWorkbook.Save
  ActiveWorkbook.Close

End Sub

Code: Select all

'Other code...    
    Workbooks(TARGETWORKBOOK).Save
    ThisWorkbook.Save
'Other code...