s_aishwarya
asked on
conditionally copy in excel VB
Hi,
I'm trying to copy some data from one worksheet to another (conditionally).
In the master worksheet, I have a lot of data in rows and columns.
One of the columns has specific information (names) and I'm trying to copy only certain rows/columns based on whats in the "names" column.
ex:
A B C D
1 John Blue Play Bulb
2 Jack Black Run Toy
3 Dave Brown Home Desk
4 Jack Grey Work Door
5 Brian Green Shed Chair
I want code that copies all rows that contain "Jack" in column A to a new worksheet. The worksheet should be named "Jack" and only certain columns should be copied, such as columns A, and C.
Therefore the new worksheet (Jack) will be:
A B
1 Jack Run
2 Jack Work
I'm trying to copy some data from one worksheet to another (conditionally).
In the master worksheet, I have a lot of data in rows and columns.
One of the columns has specific information (names) and I'm trying to copy only certain rows/columns based on whats in the "names" column.
ex:
A B C D
1 John Blue Play Bulb
2 Jack Black Run Toy
3 Dave Brown Home Desk
4 Jack Grey Work Door
5 Brian Green Shed Chair
I want code that copies all rows that contain "Jack" in column A to a new worksheet. The worksheet should be named "Jack" and only certain columns should be copied, such as columns A, and C.
Therefore the new worksheet (Jack) will be:
A B
1 Jack Run
2 Jack Work
ASKER
Can you use filter on Column 4 I.e country name,
the result should create workbooks based on unique country name and the contents of which
should be the rows with same country name.
Thank you so much
source.xlsx
the result should create workbooks based on unique country name and the contents of which
should be the rows with same country name.
Thank you so much
source.xlsx
Do you want worksheets or workbooks?
ASKER
workbooks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Your first solution creates the new excel workbooks with correct names but the contents are not filtered.
The new workbooks created contain contents of original/source workbook (10 rows) without any filtering.
Your first solution creates the new excel workbooks with correct names but the contents are not filtered.
The new workbooks created contain contents of original/source workbook (10 rows) without any filtering.
ASKER
But thanks for your response :)
On your test File it works
the code is looking for "country" should it look at "Country" or something else
the code is looking for "country" should it look at "Country" or something else
See with your file
sourceV1.xlsm
sourceV1.xlsm
Have you tried the Advanced Filter feature?
This can create an extract from a datasheet with filtered results and specific columns. The routine would then just make a copy of the result sheet into another workbook and rename it.
When using Advanced Filter in VBA it doesn't need to look at each individual line, it looks at the dataset as a whole so could be much shorter routine.
You can also change the criteria for the filter on the worksheet rather than having to change it in the script.
This can create an extract from a datasheet with filtered results and specific columns. The routine would then just make a copy of the result sheet into another workbook and rename it.
When using Advanced Filter in VBA it doesn't need to look at each individual line, it looks at the dataset as a whole so could be much shorter routine.
You can also change the criteria for the filter on the worksheet rather than having to change it in the script.
Could you send a dummy?
Regards