Macro to Open workbooks and copy data to respective sheets

[howard]

I have written code to open two workbooks and to copy the data to sheet(2) (New Inventory) and to sheet(3) (Used Inventory)

The macro allows me to select the workbooks to be copied, however, new worksheets are created and the data copied into the new sheets

1) I want the data from the first file selected to be copied to sheet(2) "New Inventory"
2) I want the data from the second file selected to be copied to sheet(3) "Used Inventory"


The source data only has one sheet


It would be appreciated if someone would kindly assist me in amending the code so that the data is copied into the correct sheets

 Sub Open_Workbook()
ChDir ("C:\downloads")
With Sheets(2)
.Range("A1:DZ5000").ClearContents
End With


Dim nb As Workbook, tw As Workbook, ts As Worksheet
A = Application.GetOpenFilename
If A = False Or IsEmpty(A) Then Exit Sub
With Application
    .ScreenUpdating = False
    End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(A)


nb.Sheets(1).Copy Workbooks("Overaged Stock Report.xlsm").Sheets(2)

ChDir ("C:\downloads")
With Sheets(3)
.Range("A1:DZ5000").ClearContents

End With

A = Application.GetOpenFilename
If A = False Or IsEmpty(A) Then Exit Sub
With Application
    .ScreenUpdating = False
    End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(A)

nb.Sheets(1).Copy Workbooks("Overaged Stock Report.xlsm").Sheets(3)

ChDir ("C:\my Documents")

End Sub

 

[howard]

I have also posted on windowssecrets.com



Macro to Open workbooks and copy data to respective sheets | Windows Secrets Lounge