Previous Topic Next Topic

Macro to Copy Data From Multiple Sheets onto Single Sheet

Closed
mhigh22 - Aug 31, 2015 at 03:47 PM
vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
 - Sep 3, 2015 at 03:27 AM
Hello,

I have multiple sheets that contain basically identical data and I am trying to extract certain parts and place it onto a single sheet. The categories I need are name (A5), age (S5) and FL License Number (W5). The multiple sheets are all named as Table 1, table 2, table 3,....etc.

If anyone can help it would be greatly appreciated, I am pretty lost.

2 replies

Answer 1 / 2
vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
 250
Sep 3, 2015 at 01:53 AM
Hello Mhigh22,

Looks like the fish are not biting!

Do you still need help on this?

Cheerio,
vcoolio.
0
Mhigh22
Sep 3, 2015 at 03:15 AM
Yes unfortunately I am still stuck and would greatly appreciate any help I can get! Thank you!
0
Answer 2 / 2
vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
 250
Sep 3, 2015 at 03:27 AM
Hello Mhigh,

Try the following code to see if we are at least going in the right direction:- 

Sub TransferData()

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim lRow As Integer

For Each ws In Worksheets
    If ws.Name <> "Summary" Then
        Sheets(ws.Name).Select
        
 For Each cell In Range("A5:A" & Cells(Rows.Count, "A").End(xlUp).Row)
            If cell.Value <> "" Then
                lRow = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row + 1
                Range(Range("A" & cell.Row), Cells(cell.Row, "A")).Copy Sheets("Summary").Range("A" & lRow)
                Range(Range("S" & cell.Row), Cells(cell.Row, "S")).Copy Sheets("Summary").Range("B" & lRow)
                Range(Range("W" & cell.Row), Cells(cell.Row, "W")).Copy Sheets("Summary").Range("C" & lRow)
                Sheets("Summary").Range("D" & lRow).Value = ws.Name
            End If
    Next cell
End If
Next ws

Sheets("Summary").Select
Application.ScreenUpdating = True
Application.CutCopyMode = False
            
End Sub


Following is a link to my test work book for you to peruse:-

https://www.dropbox.com/s/1n502clcwqori0j/MHigh22.xlsm?dl=0

As you can see from the test work book, the three categories are transferred from each sheet to the "Summary" sheet. The code also brings across the source sheet name so that you can instantly see from which sheet the data comes from.

There is another code in module 2 which does the same task (except for bringing across the source sheet name). Just ignore that one for now.

I hope that this helps.


Cheerio,
vcoolio.
0