Copy data from header to header in consolidation sheet

**Ignesh** · 06-29-2013, 11:34 PM

Hi,

I have the data in multiple sheets with mulitple columns, the data has to be consolidated in the consolidated sheet. All the data has to read header name in each column and corresponding data has to be copied in the consolidation sheet. This is just a sample file and in every WS, I would be having nearly 150 to 200 columns. The macro should copy paste the data from each WS to the consolidated sheet.

please help me on this.

thank you.

**HaHoBe** · 06-30-2013, 02:45 AM

Hi, Ignesh,

only those column headers that are to be found in sheet consilidation? Or shall the Headers/Columns be updated there as well (Sheet4 shows address)?

Ciao,
Holger

**Ignesh** · 06-30-2013, 10:37 AM

Hi Ciao,

Only the headers which are in consolidation has to be found out and data of those columns have to be retrieved to the consolidation sheet.

Sorry for not being clear in the first note.

**john456852** · 06-30-2013, 10:41 AM

Please Login or Register  to view this content.

should do the trick

**HaHoBe** · 06-30-2013, 10:59 AM

Hi, Ignesh,

you maybe have to come back regarding the items in the headers as this is not to copy a lot of information from the other sheets.

Code may look like this

Please Login or Register  to view this content.

How to install your new code

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

Ciao,
Holger

**Ignesh** · 06-30-2013, 09:39 PM

While running the macro, I am getting error message. The error message is 400.

**HaHoBe** · 07-01-2013, 12:34 AM

Hi, Ignesh,

did you test the code on the sample or your real workbook? I have tested the code on the sample on both Excel2013 as well as Excel2003 and could not raise that error. Is there protection set for any of the worksheets? Could you specify wheree exactly the error is raised (line should be marked in yellow)? Is there any data copied over or is it being raised before any copying?

Ciao,
Holger

**Ignesh** · 11-12-2013, 03:50 AM

Hi,

The macro is running but there is no data copied on the consolidation sheet. Cells are blank.

**AB33** · 11-12-2013, 04:10 AM

Ignesh,
Try the attached. Some of the headers in the consolidation sheet do not match with headers in individual sheets. The code works on EXACT MATCH ONLY.