Copy column data from multiple worksheets to single worksheet based on header criteria

[nateaskins]

Hi,

I've spent about an hour searching this forum and the internet, but I can't seem to find someone with the exact same situation. I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.


I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.

Sub copycolumns()
Dim i As Integer, searchedcolumn As Integer, searchheader As Object


For i = 1 To 5

Set searchheader = Sheets(12).Cells(1, i)

searchedcolumn = 0
On Error Resume Next
searchedcolumn = Sheets(1).Rows(1).Find(what:=searchheader.Value, lookat:=xlWhole).Column
On Error GoTo 0

If searchedcolumn <> 0 Then
Sheets(1).Columns(searchedcolumn).Copy Destination:=searchheader

End If

Next i
End Sub

[arlu1201]

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

Will the 5 columns be constant or the same in all the sheets? Why do you want to search each time? Wont they be in the same location each time the report is prepared?

[nateaskins]

Thanks for the welcome, and I apologize for not reading the rules more carefully. I will use the code tags in the future, for sure. I also apologize for my tardiness in reply, I've been traveling without internet for several days.

The text of the 5 column headers that I am trying to grab are the same; however, the order and number of columns will vary from sheet to sheet. This means, for example, one of my desired columns may be in column 4, or may be in column 7, depending on how that sheet was generated, which is beyond my control.

I don't totally understand your question, and I think it's probably because I wasn't clear enough in the original post. To restate hopefully with more clarity, I want to run this process once, to process a number of sheets with similar yet different structures and assemble the data in a single sheet based on my 5 column criteria.

[JOHN H. DAVIS]

Can you attach a workbook with some sample data showing before and after result. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[nateaskins]

Example.xlsm

I've attached a file. What's going on here is the first sheet is building different urls that will query google places API and return results based on differing keywords for businesses in a specified geographic radius. Then, I have a macro that creates a new worksheet, grabs a URL from the first worksheet, runs a web query with that URL, which returns an xml table that is populated in the newly created worksheet. There are 8 urls, so 8 worksheets are created. The problem is that some businesses return different parameters, so you will find all the worksheets are largely similar, but some have different columns that the others may or may not have, so consequently, there is not an exact similar structure shared amongst the worksheets.

[JOHN H. DAVIS]

I'm a bit confused. In post #1 you wanted to consolidate information in Sheet12, by identifying the proper heading in each sheet and aligning in up to the format in Sheet12? The attachment doesn't reflect that. Or maybe I'm way off?

[nateaskins]

Sorry, I modified the workbook to make it more understandable, but apparently failed, haha. If you add a worksheet to the one I attached at the far right, it'll become sheet 10, so consider that the sheet that should be referenced by that code if the code I originally posted is to be used.

[JOHN H. DAVIS]

OK. What are the five Column Headers to pull from in each sheet? How should they appear in Sheet 10.

[nateaskins]

Example_edited.xlsmAh! I'm really sorry for being so obtuse. I've redone the worksheet, and have included the 5 headers in worksheet titled "master". It's attached.

[JOHN H. DAVIS]

Does this do what you want? Again I might be way off.

Sub nateaskins()
Dim i As Integer
Dim rcell As Range
Dim aa As String
Dim bb As String
Dim cc As String
Dim dd As String
Dim ee As String
Application.ScreenUpdating = False
aa = Sheets("Master").Range("A1").Value
bb = Sheets("Master").Range("B1").Value
cc = Sheets("Master").Range("C1").Value
dd = Sheets("Master").Range("D1").Value
ee = Sheets("Master").Range("E1").Value
For i = 1 To 8
    Sheets(i).Activate
    For Each rcell In Range(Cells(1, 1), Cells(1, ActiveSheet.UsedRange.Columns.count))
        Select Case rcell.Value
            Case Is = aa
            Range(rcell.Offset(1), rcell.Offset(ActiveSheet.UsedRange.Rows.count)).Copy Sheets("Master").Range("A" & Rows.count).End(3)(2)
            Case Is = bb
            Range(rcell.Offset(1), rcell.Offset(ActiveSheet.UsedRange.Rows.count)).Copy Sheets("Master").Range("B" & Rows.count).End(3)(2)
            Case Is = cc
            Range(rcell.Offset(1), rcell.Offset(ActiveSheet.UsedRange.Rows.count)).Copy Sheets("Master").Range("C" & Rows.count).End(3)(2)
            Case Is = dd
            Range(rcell.Offset(1), rcell.Offset(ActiveSheet.UsedRange.Rows.count)).Copy Sheets("Master").Range("D" & Rows.count).End(3)(2)
            Case Is = ee
            Range(rcell.Offset(1), rcell.Offset(ActiveSheet.UsedRange.Rows.count)).Copy Sheets("Master").Range("E" & Rows.count).End(3)(2)
        End Select
    Next rcell
Next i
Application.ScreenUpdating = True
End Sub

[nateaskins]

Flawlessly. Thank you, very much, John!

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

[ASharp1971]

Hi. This is my first forum post... I have a similar requirement to the above and I am not familiar with using VBA in Excel. I need to generate a report from multiple sheets/tabs in a single workbook. There is specific criteria to follow when extracting to the report i.e each tab represents a Task assigned to a team and within that specific task there are action items assigned to one or more team members. Each tab is the same layout with a header row for the range to search and within the range there are action items identified and Open/Close status assigned. I need to update a report with only Open Action Items while also identifying the tab it came from (on another worksheet) after I have updated each tab with the most recent information. I know there is a way... I just haven't learned enough about Excel to do it myself yet. Can you help?

[JOHN H. DAVIS]

Can you attach a sample file with some dummy data? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[ASharp1971]

9 Box Tasks - Tool V1.0 UNIX 20MAR14.xlsm

Here's a sample John.

Thanks,
Angie

[JOHN H. DAVIS]

Angie. Perhaps you should start a new thread for this. Others who may be able to help may overlook because the thread is marked as SOLVED. BTW what determines an Open item?

[ASharp1971]

John, I can start a new thread... but it will be later today. I determine open tasks from the task tab and status, then open actions for each task is on the specific tab for the task listed as a action item and Open status.