asked on

Create multiple excel workbook in loop

Hi Experts,

I have access VBA code to collect necessary data to another sheet of the same workbook.
before save and close the sheet, I need to create excel workbooks using the names in the collected sheet.

for example, test.xlxs has two sheets all_data and filtered_data. The sheet filtered_data has cell contain some string like alpha, bravo, charlee, delta.
I want to create excel workbooks named alpha.xlxs, bravo.xlxs, charlee.xlxs and delta.xlxs ,
it will create all strings of that cell in loop then save and close the test.xlxs.
copy some related data to the created excel workbook with a sheet name(use the workbook name)
Set wb_Source_01 = xl.Workbooks.Open("C:\test.xlsx")
Set ws_Source_01 = wb_Source_01.Sheets("all_data")
Set ws_Source_02 = wb_Source_01.Sheets("filtered_data")

first copied necessary data from all_data sheet to filtered_data sheet like below.

ws_Source_02.Cells(LineCounter, 1) = ws_Source_01.Cells(LineCounter, 1)
ws_Source_02.Cells(LineCounter, 2) = ws_Source_01.Cells(LineCounter, 2)

Once all necessary data copied to sheet filtered_data before save and close the workbook, wants to create excel workbook name it one of the cell value of the filtered_data sheet above. then save some related data from the filtered data to that newly created sheet then save it continue until all data of that cell if there 10 then wants to create 10 excel workbooks with the name of the cell in the filtered_data sheet.

Once done finally save and close the work book test.xlxs.

Please advice vba code to do that.

Thanks

Norie

Can you just clarify what you want to do?

You have a sheet, 'all_data', that has all the data and another sheet, 'filtered_data', that only has a few strings in cells.

You want to create a new workbook for each of the strings in 'filtered_data' and extract the data from 'all_data' the relevant sheet based on some criteria.

If that's what you have, how can it be determinend which workbook the data from 'all_data' should go to?

Is it based on a specific column?

Rey Obrero (Capricorn1)

@alam747,

this can be clearly understood if you can upload a copy of the excel file..

.

alam747

ASKER

Hi Imnorie,

You're are correct, the data pull from filtered sheet to the newly created workbook the filtered sheet have have related filed there is a key that use to pull from.

the data in filtered sheet like as:
alpha 001 other related informamtion which will be copied in the newly cerated workbook alpha.xlsx
bravo 002 other related information which will be copied in newly created workbook bravo.xlsx

Therefore I can use the code 001 to pull entire cells of the raw to alpha.xlsx workbook.

Please provide me VBA code to do that in a loop.

Thanks

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alam747

ASKER

Thanks