I need a macro that can extract data

Closed
excelmwangi Posts 16 Registration date Friday February 6, 2015 Status Member Last seen June 5, 2015 - Feb 7, 2015 at 06:47 PM
vcoolio Posts 1371 Registration date Thursday July 24, 2014 Status Moderator Last seen April 12, 2023 - Mar 26, 2015 at 09:46 PM
Hello,

I need help with creating a macro that can automatically copy data from different worksheets and put it into a master worksheet for a report. The problem is that it is on multiple rows on different worksheets and I don't want it to leave empty rows between if I just use the usual worksheet copy way. Please help

4 replies

vcoolio Posts 1371 Registration date Thursday July 24, 2014 Status Moderator Last seen April 12, 2023 252
Feb 8, 2015 at 06:26 AM
Hello excelmwangi,

The easiest way to do this would be to add a selection criteria to your dataset. So, in the following code, I have added an asterisk "*" in Column A (for each sheet) as the criteria.


Sub CopyData()
Dim ws As Worksheet
Dim lRow As Long
Dim lCol As Integer

Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name = "Master" Then GoTo NextSheet
    
    ws.Select
    
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    For Each cell In Range("A2:A" & lRow)
        If cell.Value = "*" Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Copy
            Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
    Next cell

NextSheet:
Next ws
Sheets("Master").Range("A1:A" & Rows.Count).ClearContents
Sheets("Master").Select

Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "Data transfer complete", vbExclamation
End Sub


With this code, whichever row you place an asterisk by (ensure this is in Column A), in whichever sheet, the row of data will be copied to the "Master" sheet. The criteria is left behind.

You can have a look at my Test work book here:-

https://www.dropbox.com/s/dfy344w9u1w0509/Excelmwangi.xlsm?dl=0

to see if it works for you.

We can add an additional row of code if you want the transferred data cleared from the worksheets supplying the "Master" sheet. As the code is now, it clears the "Master" sheet prior to the data transfer to prevent duplicates while the data remains in each of the supplying sheets.

Kind regards,
vcoolio.
0
excelmwangi Posts 16 Registration date Friday February 6, 2015 Status Member Last seen June 5, 2015
Feb 9, 2015 at 12:55 PM
Vcoolio, thank you for taking the time. However, when I copied the code and added the criteria, it is still not updating. Am I doing something wrong. Can I upload the document for you to see what's going on? Appreciate the help
0
excelmwangi Posts 16 Registration date Friday February 6, 2015 Status Member Last seen June 5, 2015
Feb 9, 2015 at 01:03 PM
Vcoolio, thank you for taking the time. However, when I copied the code and added the criteria, it is still not updating. Am I doing something wrong. Can I upload the document for you to see what's going on? Appreciate
0
excelmwangi Posts 16 Registration date Friday February 6, 2015 Status Member Last seen June 5, 2015
Feb 9, 2015 at 01:35 PM
Hey, Here is the link to my document. I put the * to the rows I want copied maybe you can help me diagnose and test. Thanks again.

https://www.dropbox.com/s/azbbvcnp4e8flsp/Planning%20Worksheet%20Western%20Vicariate%20-%20Copy.xlsm?dl=0
0