Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet

**casper3043** · 10-01-2015, 12:52 PM

I've seen a few examples done, but I can't seem to make them work for my scenario.

I have 8 worksheets that I need to take data from 3 columns (based on the column headings since the header names aren't always in the same columns) and paste*special them into a new worksheet.

I have attached an example to help illustrate this.

1) I need the data from Sheets 1 - 8 for the specific column headers: "Full name2", "Office Location", and "Grand Total"
2) Would like to paste the data into "Consolidated" worksheet, which has the 3 column headers defined in that worksheet.
3) The "Grand Total" column is what changes on Sheets 1 -8 making this difficult for me.

Appreciated as always. This is probably something basic for you macro expert, but has been throwing me for a loop.

**stnkynts** · 10-01-2015, 01:11 PM

Try this:

Sub Consolidate_Data()
Dim arr As Variant
Dim wsMain As Worksheet:   Set wsMain = Sheets("Consolidated")
Dim ws As Worksheet
Dim i As Integer
Dim r As Range
Dim lc As Long, lr As Long

Application.ScreenUpdating = False

arr = Array("Full Name2", "Office Location", "Grand Total")

For Each ws In Worksheets
    If ws.Name <> wsMain.Name Then
        lc = ws.Cells(7, Columns.Count).End(xlToLeft).Column
        For i = LBound(arr) To UBound(arr)
            Set r = ws.Range(ws.Cells(7, 1), ws.Cells(7, lc)).Find(arr(i), , xlValues, xlWhole)
            If Not r Is Nothing Then
                lr = ws.Cells(Rows.Count, r.Column).End(xlUp).Row
                ws.Range(ws.Cells(8, r.Column), ws.Cells(lr, r.Column)).Copy wsMain.Cells(Rows.Count, i + 1).End(3)(2)
            End If
        Next i
    End If
Next ws

Application.ScreenUpdating = True
  
End Sub

Note: You didn't mention combining names and locations so I just simply followed your directions to the letter

**casper3043** · 10-01-2015, 02:06 PM

thank you stnkynts...i'm a lot closer than i was before.

when i run the macro, it pastes all the data except for "Full name2" and "Office Location" for the last 3 worksheets. In my real workbook, I have these worksheets named something other than Sheet1, Sheet2, etc. I was thinking if there was a way to name which worksheet I want to copy from to avoid any confusion? Otherwise, this macro worked in the test example.

**casper3043** · 10-01-2015, 03:06 PM

did a little editing and got it...thanks stnkynts for your help!

Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet

Re: Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet

Re: Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet

Re: Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet

Thread Information

Users Browsing this Thread

Similar Threads

copy and paste columns based on column headers

[SOLVED] (Excel VBA) Copy and Paste from one worksheet to multiple worksheets by names

macro to add columns after existing columns and copy/paste headers and formulas

[SOLVED] Copy/Paste multiple columns as values based on another columns criteria

[SOLVED] Copy and Paste rows to multiple worksheets based on a cell value.

[SOLVED] Copy multiple worksheets data & paste into blank columns in summary worksheet

Copy data from multiple worksheets & paste into one worksheet.

Bookmarks

Bookmarks

Posting Permissions