Macro to import workbook to sheet tab based on name

[Howardc1001]

I have several workbooks that I wish to import and copy to the destination workbook. I have written a macro to do this, but want the macro amended so that each sheet imported is pasted on a seperate sheet based on the name of the workbook limited to the first 20 characters

I have attached my source data (BR1 Stock Due.xls) as well as my destination workbook


Your assistance in this regard is most appreciated

[Eastw00d]

Hi
given the data you get better results if you adapt your macro like this:

Please Login or Register  to view this content.

Cheers
Erwin

[Howardc1001]

Thanks for amending my code Erwin

What I what looking that each workbook to be copied into a separate sheet based on the source workbook name 1st 20 characters only for eg if source workbook is BR1 South Stock sales Reporting for period ending 31/03/2020 then sheet to be created called BR1 South Stock Rep and the data from this work to be copied to this sheet etc


Your assistance in this regard is most appreciated

[Eastw00d]

Hi please see following, you have to make a list of files, but this will only work if you rename your files accordingly...
Why not export a list of files from Windows with the real names, adapt it to your needs and put this in your workbook?
To do this: in the command screen of windows:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Cheers
Erwin

[jindon]

Try

Please Login or Register  to view this content.

[Howardc1001]

Thanks for the help and your input

[Howardc1001]

Thanks for the help Jindon, code works perfectly