Macro button to copy data from other worksheets in the workbook to summary sheet

[cme]

Hi,

I am trying to create a macro that will extract data from all the worksheets in a workbook to a summary sheet (which occupies Sheet1).

In a nutshell, I have several worksheets in a file that I adjust individually from time to time, they all maintain the same exact format with same titles/columns, etc.; the data starts on row 14 of each of these worksheets and will have a date (MM/DD/YYYY) in column C along with paired information in the row (columns B through G). Some worksheets may only have 5 rows of data, others may have 80 rows of data, they are all different.

Now I have created a summary sheet and want to put a button that says "Update" and when I click it, it will go and search each worksheet from row 14 and down and if there is a value (or is not empty) then it will copy those details (B through G) to the summary page, one after another. Which then create a large database of all the Dates & Actions from all the worksheets that I can use to auto-filter.

Appreciate any and all guidance!!

 

[Smitty]

Welcome to the Board!

If you can't put all of the data on one sheet, then why not try a Pivot Table with multiple consolidation ranges? You can invoke the wizard with Alt+D+P.

HTH,

 

[cme]

Hi Smitty,

Thanks!

I tried a Pivot but it doesn't suit my needs perfectly. To explain further, I am building a format that will be used by several different employees, each has a different business account to manage and will have a various amount of worksheets. Each worksheet lays out the info for a single purchase order while the summary page lists out all details to all POs.

The catch:
The data on the worksheets are organized differently on the summary sheet (different columns & more columns on the summary sheet). In fact I have already formed a code that is working somewhat close to what I need:

Sub Button1_Click()
Dim WkSht As Worksheet
Dim r As Integer
For Each WkSht In ThisWorkbook.Worksheets
If WkSht.Name <> "Summary" Then
For r = 14 To 200
'This will check the rows 14 to 200 of each sheet
If WkSht.Range("C" & r) <> "" Then
WkSht.Rows(r & ":" & r).Copy Sheets("Summary").Range("A65536").End(xlUp).Offset(3)
End If
Next r
End If
Next WkSht
End Sub


As you can see I linked it to a button, so when I click it, it should go and extract all the data from every sheet in the workbook other than Summary and pull rows 14 onward. I have 3 issues:
1) it is copying all the rows on top of each other (replacing each one) so after the macro completes I have just watched all the rows copy on top of each other and the result is 1 row of the last info copied.
2) How can I specify "only copy columns B through G" as opposed to "the whole row"
3) How can I specify where to begin placing the copied info, it pops up in column A, row 5, but I want to make it start at Column C, row 7

Thank you so much for your help!
Cme

 

[Smitty]

1) Instead of looping through rows, which is really inefficient, I'd record a macro applying a filter to column C to exclude blanks. Then add that to the code.

2) If you set r as the last row wksht.cells(rows.count,"C").end(xlUP).Row then you can use Range("C14:G" & r).SpecialCells(xlCellTypeVisible).Copy

3) You'll probably need to add an if statement to identify if the first destination row is #7; if so, paste there, otherwise use the end(xlUp) method

 

[cme]

I am afraid I am very new to VBA/macros (started 48 hours ago) would you mind walking me through it a little more?

I actually got the above code from another thread and modified it for my needs so I am not entirely sure what each component does; only beginning to understand.

We can ignore (3), number (1) & (2) are most important

 

[phantom1975]

Give this a whirl! Paste the code in the worksheet module of the summary sheet.

Private Sub Worksheet_Activate()

    If MsgBox("Would you like to compile the summary sheet?", vbYesNo + vbQuestion, "COMPILE SUMMARY SHEET") = vbYes Then
    
        Call CompileSummary
        
    End If
    
End Sub

Sub CompileSummary()
Dim Sh As Worksheet

    Me.Cells.ClearContents ' Clear summary sheet of old data
    
    Me.Range("B1").Value = "Hello!  I am your lucky placeholder!" ' Placeholder needed so the program will "know" where to start pasting data
    
    ' The code below copies the rows from every worksheet starting in row 14 all the way down to the last entry in the worksheet
    ' and pastes it into the first blank row on the summary page
    
    For Each Sh In ThisWorkbook.Sheets
    
        If Sh.Name <> Me.Name Then
        
            Sh.Rows("14:" & Sh.Range("B65536").End(xlUp).Row).Copy Me.Range("B65536").End(xlUp).Offset(1).EntireRow
    
        Me.Cells.Sort _
        Key1:=Me.Range("B1"), Order1:=xlAscending '  Sort the data so any blank rows show up at the bottom
            
        End If
        
    Next Sh
    
    Me.Rows(1).Delete ' Placeholder no longer needed
    
    Me.Columns("H:IV").Delete ' Delete any data outside of column G
    
    Me.Columns("A").Delete ' Delete column A

End Sub

 

[cme]

Hi Phantom! Glad you could help!

So I plugged the code in and an error came up saying, "Compile error: Invalid use of Me keyword"
I thought it may have been because there was no "Dim Me As Worksheet" but that gives me a syntax error so I am assuming "Me" is a special designation in VBA? Let me know what I should do next, I use MS Excel '07

Thanks!

(EDIT) Additionally, I noticed the clear contents command looks as if it would clear the whole sheet but there are titles/column headers that I would like to preserve in the clear, everything below row 5 (row 6 and onward) would be the area to clear.

 

[phantom1975]

This first thing you will need to do is change

Me.Cells.ClearContents

to

Me.Rows("6:65536").ClearContents

The reason you are getting the error is that you pasted the code into a regular module instead of the worksheet module. HEY! IS THERE ANYONE OUT THERE THAT CAN TELL ME THE, UM, MODULE MODULE IS TECHNICALLY CALLED?

So open your workbook and then push ALT + F11. This opens the Visual Basic Editor. On the left side you will see the project window and there will be some folders. One of the folders is called, "Microsoft Excel Objects". In this folder are all of the worksheets in your workbook and a module called ThisWorkbook. When you record a macro, it is usually put into a module called, "Module1" in the modules folder.

Technically, you can put code in any of these modules (one of the worksheet modules, ThisWorkbook module, Module1, etc.) It is usually a good idea to put code that is specific to a particular worksheet in the module for that worksheet.

There are some coding habits that I encourage. One of them is to be specific even when you don't have to be. For example, I could have a line of code in a workbook module that says, Range("A1").Value = "Hello!". When this line of code is executed, it will put Hello into cell A1. I can be more specific and say Sheets("Sheet1").Range("A1").Value = "Hello". I can even go farther by saying Thisworkbook.Sheets("Sheet1").Range("A1").Value = "Hello". All of them do the same thing.

The shortcut, "Me" is a way of saying that you are referring to the module in which the code is written. This really becomes handy when you start creating UserForm objects. So if I have Range("A1").Value = "Hello" in the Sheet1 Module, then the "Me" in Me.Range("A1").Value is referencing the module it is in, or Sheet1.

In order for the code to work, you need to paste it into the module for the worksheet that will have the summary data in it. By the way, there is no button to run the code. The code automatically runs when you activate the worksheet. That is called a trigger or, more commonly, an event. An event can be when you click a cell, change a value, activate a sheet, deactivate a sheet, and all sorts of other things.
</pre>

 

[phantom1975]

You will also need to change the B1 ranges to B6 and change the

Me.Rows(1).Delete

to

Me.Rows(6).Delete</pre>

 

[cme]

Hey Phantom,

Thanks for the explanation I appreciate it!

SO, I made the changes as you said and I put it into the 'ThisWorkbook' module (and also tried it in the specific "Summary Sheet"), the good news is that it ran this time; no error message. The bad news is it is doing something really funky. It takes about 2 minutes to run and then displays a series of things copying over other things and things deleting and repeating and the final result is a bunch of jumbled cells occupying cells A1 through F and down.

It is worth mentioning that I have created/assigned checkboxes in column 'A' of these worksheets that when you copy/paste it also copies that checkbox which makes for a "clunky" and awkward paste because the checkboxes are all linked to a column of data etc and are actually not 'selectable' within the columns since they are on a separate 'layer' (for lack of a better term).

Additionally, I noticed it is overwriting the title/column header rows on the summary sheet; i.e. all the data is being copied into cell "A1:F" and down, overwriting the title/date/etc that normally occupies that top-of-the-page/header area. It is also pulling the column headers from the worksheets.


(PS Is there a way I can send you the file to show you?)