Open WB, copy each sh. & paste into other WB, regardless of tab na

D

Driftwood

I have this code so far, which opens a WB, copies a sheet & pastes into my
WB's appropriate sheet.

Sub Get49Data()

Workbooks.Open Filename:= _
"C:\Receiving Inspection\SHIPPING\Supplier49 shipping doc.xls"
Cells.Select
Selection.Copy
Windows("Supplier Report.xls").Activate
Sheets("Supplier49a").Select
Cells.Select
ActiveSheet.Paste
End Sub

What I need it to do is copy each worksheet in Supplier49's workbook,
regardless of the (tab) name (as the supplier changes it inconsistently each
time).
Then I need it to paste each of those sheets into an awaiting worksheet
(supplier49a, supplier49b etc) in my workbook - where the cover page
(REPORT) picks up all cells I need from each suppliers list with the
formula: =IF ('Supplier49a'! D13<>"",'Supplier49a'! D13,"").
The report then uses auto filter for all 50 suppliers in the REPORT page to
filter through what we need.
Your help is much appreciated
Thank You
Driftwood
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top