Macro to Copy Data From Multiple Sheets onto Single Sheet
Closed
mhigh22
-
Aug 31, 2015 at 03:47 PM
vcoolio Posts 1371 Registration date Thursday July 24, 2014 Status Moderator Last seen April 12, 2023 - Sep 3, 2015 at 03:27 AM
vcoolio Posts 1371 Registration date Thursday July 24, 2014 Status Moderator Last seen April 12, 2023 - Sep 3, 2015 at 03:27 AM
Related:
- Macro to Copy Data From Multiple Sheets onto Single Sheet
- How to copy data from one sheet to another in excel automatically - Guide
- How to screenshot excel sheet - Guide
- Little alchemy cheat sheet - Guide
- Google sheet right to left - Guide
- Excel macro to create new sheet based on value in cells - Guide
2 replies
vcoolio
Posts
1371
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 12, 2023
252
Sep 3, 2015 at 01:53 AM
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.
Looks like the fish are not biting!
Do you still need help on this?
Cheerio,
vcoolio.
vcoolio
Posts
1371
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 12, 2023
252
Sep 3, 2015 at 03:27 AM
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:-
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.
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.
Sep 3, 2015 at 03:15 AM