copy active workbooks to a paste to closed consolidation workbook

Hi All

I have several purchase orders workbook daily, need to do some data entry once completed save it , copy the actual range to a consolidation summary workbook in a specified directory.

I am looking a vba code for the po sheet in the workbook, copy the actual range on that sheet only with formats and values only and paste it in the closed workbook consolidation summary workbook.

However the vba code should be modulable on the headers as there are other purchase orders with more headers and some with less headers and consolidation summary directory differs

Note the po files have other sheets.

The headers are same for for po workbook and consolidation workbook found C:\\SUMMARY \consolidation summary.xlsx

Any assistance would be grateful as this will save me a lot of time

Hi Imran

Have google for this but indeed my knowledge in coding language is low.Trying to learn with what I do at work .

Hi Sinetek

I will upload amended files for ease of undestanding in a few minutes

Perphaps my explanation was confusing .

as per post 6 with the amended examples, Is it possible to a have sample as per post 6 amended files .

That is with sample files poa1 copy the active sheet to the closed file c:\\ summary\ consolidatea summary.xlsx .only

can you have a try and advise

JEAN1972,

Are the headers of each file looks always the same?
If so it should be very easy.

Hi Jindon

The headers always the same as per amended sample files po1a, po2b and summary consolidatesummary.xlsx.

NOTE for po1A.xlsx column V add header 21 I missed that sorry

all worksheet will have same headers .

Do note take into account my modulable header request as it is causing confusion.

Just look at the sample files provided that is active sheet po1a.xls copy activesheet to closed workbook c:\\ summary\ consolidatea summary.xlsx .only
There is only one sheet in the closed workbook.

Hope it clearer now.

Advise if you need further clarifcation

I had a same issue. And just a few minutes ago JOHN H. DAVIS helped me to fix my code.

Try this code exactly. Hope it works

Please Login or Register  to view this content.

Hi Sintek

There are 21 headers I just missed one on po1A .xlsx otherwise All will have same orders .

All are copied in same column order

Advise if u need further clarifcation

Hi orhanceliloglu

I have a try and advise

JEAN1972
To po1A or 2A and if this is how you wanted, you could save the code onto PERSONAL.XLSB, so that you could run from each file.

Please Login or Register  to view this content.

Originally Posted by ImranBhatti

I think there is a difference between your query and that of JEAN's

Maybe I couldnt understand JEAN's query. I am attaching my excel to Jean's review.

HI orhanceliloglu

Try code , your approach is good but it copies and create a Master in the same workbook

So that po1A.xlsx , I make my data entry and then activate the macro to copy the active sheet range to the closed workbook which is found on directory on C:\\ summary consolidatea.xlsx.

The summary file has already their headers.

Then same action opened po1B.XLSX and copy active sheet on which is found on directory on C:\\ summary consolidatea.xlsx.

A little tweak need to be done to copy to the closed workbook on on C:\\ summary consolidatea.xlsx.c without overwriting existing data in consolidation workbook.

JEAN1972

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thank you all for assistance and time dedicated to solve this issue