Copy

[balaji3081]

Hi All.

I am looking for a macro to find the text in Col A, and copy multiple cells based on a column heading for eg as in the below table

Look for text -
"Total Sales X country"
"Total Sales Y country"

and from the same rows copy from cells with column heading as Quater 1 and Quater 2 and paste into a new file ( All data.xlsx)
NOTE
- All data file has the heading Quarter 1 and Quater 2 already mentioned.
- the heading change month over month so wanted it to mentioned outside the macro.

Any help appreciated here , Thanks

	Jan	Feb	Mar	Quarter1	Apr	May	June	Quarter 2
text1	3	4	5	12	7	9	5	21
text2	2	5	6	13	1	1	1	3
text3	3	3	3	9	2	4	5	11
text3	4	6	3	13	4	1	5	10
Total sales X country	11	18	17	46	14	15	16	45
text4
text5
text6
Total sales Y country	4	4	4	12	3	3	3	9

 

[balaji3081]

Appreciate any help here, thanks

 

[kevin9999]

Where exactly in the All data.xlsx file do you want it copied to (to what sheet name, to which cell(s))?
Also, I don't understand what "the heading change month over month so wanted it to mentioned outside the macro" means?

 

[balaji3081]

Thanks Kevin for replying.

For this question purpose the table starts in Cell B2, so you can take it as B3 is where start pasting the data

In All data file I should be able to change the heading from Quarter 1 to Full year or to any month and still get the corresponding data

 

[kevin9999]

Thanks Kevin for replying.

For this question purpose the table starts in Cell B2, so you can take it as B3 is where start pasting the data

In All data file I should be able to change the heading from Quarter 1 to Full year or to any month and still get the corresponding data

You've answered the second part of my first question (which cells to copy to) but you still haven't said to which sheet.

Where you say "In All data file I should be able to change the heading from Quarter 1 to Full year or to any month and still get the corresponding data", does that mean that you want to be able to change the headings in the All data file and return all columns that match whatever your headings in the All data file are?

Also, will you be running the code from the file that contains the table in post #1? If so, what is the sheet name of the sheet where the table is located?

 

[balaji3081]

All data tab has only one sheet = "Summary"

Where you say "In All data file I should be able to change the heading from Quarter 1 to Full year or to any month and still get the corresponding data", does that mean that you want to be able to change the headings in the All data file and return all columns that match whatever your headings in the All data file are? - "YES"

The table in post one is from All data tab and the tab name is - "Summary"

The data from multiple files which needs to be copied are in tab name - "Data" - for coding purpose itr can be names as Product 1, Product2, product3, Product4.....

Hope this helps

 

[kevin9999]

I'm sorry but your explanation has only served to confuse me further. I'm afraid I'm going to have to leave this to others who may have a clearer understanding of your requirements. Thank you and good luck.