Export Worksheets from Source Workbook into a new Workbook

[dhaval3547]

Hi everyone,

I was wondering if someone can please help me with a VBA code to export worksheets from source workbook to a new workbook. Please see below specific points for what I have and what I need, apologies in advance if I this is not very clear.

What I have;
A source workbook with 6 worksheets named A, B, C, D, E and F
There are extrernal links within the source workbook

What I need;
Export only visible cells from worksheet B, C and D into a new workbook
Retain the data into its seperate worksheets in the new workbook
Delete any external links in the new workbook
Copy & Paste Special Value data
All the formats should stay as it is apart from any 'Fill Colour' which need to be 'No Fill'

Thanks for your help

[AlphaFrog]

"Export only visible cells from worksheet B, C and D into a new workbook"

Are there hidden or filtered data on the sheets (B,C,D) and you only want to copy only the visible (unhidden) data?

[dhaval3547]

Hi & Thanks for your reply.

Yes, thats right worksheets (B,C,D) include data which is hidden & filtered.

I also forgot to mention that, vba code should have no impact on the source workbook, any format changes once data is copied over in the new workbook should only be on the new workbook, ideally there should be no change in source workbook after the macro has been run.

Thanks

[AlphaFrog]

Try this...

Please Login or Register  to view this content.

[dhaval3547]

Thanks a lot of this code, works like magic.

I am trying to understand the mechanics of this code, correct me if I am wrong. The code is creating a new workbook with specified number of worksheets and tab names and after that it is coping over data from the corresponding tab name in the source workbook.

Is there a way I can retain the column width and row height?

Thanks