Transfer data from multiple sheets to one sheet.
Closed
Darlz
Posts
1
Registration date
Friday March 11, 2016
Status
Member
Last seen
March 12, 2016
-
Mar 12, 2016 at 04:47 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Mar 12, 2016 at 07:14 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Mar 12, 2016 at 07:14 PM
Related:
- Transfer data from multiple sheets to one sheet.
- Free fire transfer - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Google sheet right to left - Guide
- Windows network commands cheat sheet - Guide
- Mark sheet in excel - Guide
1 response
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Mar 12, 2016 at 07:14 PM
Mar 12, 2016 at 07:14 PM
Hello Darlz,
The following code may do the job for you:-
The code selects each row of data in each sheet based on having a value (date) in Column C and summarises these rows in sheet4.
I'm not sure if you wanted the "used" data in each individual sheet removed once transferred to sheet4. This would be a good idea otherwise you'll end up with multiple duplicates in sheet4. In the above code, you'll see a line in green font (line 21). This is the line of code that will remove the "used" data from each individual sheet. It is currently de-activated but if you would like to activate this line of code, then simply remove the apostrophe at the begining of the line.
Following is a link to my test work book for you to peruse. Click on the button in sheet4 to see the code at work. You can actually execute the code from any sheet:-
https://www.dropbox.com/s/h73to6qkhl2ies1/Darlz%28Transfer%20from%20multi%20sheets%20to%20Summary%20sht.%29.xlsm?dl=0
I hope that this helps.
Cheerio,
vcoolio.
The following code may do the job for you:-
Sub SummariseData() Application.ScreenUpdating = False Dim ws As Worksheet Dim lRow As Long Dim lCol As Integer Dim cell As Range For Each ws In Worksheets If ws.Name <> "Sheet4" Then Sheets(ws.Name).Select lRow = Range("A" & Rows.Count).End(xlUp).Row lCol = Cells(1, Columns.Count).End(xlToLeft).Column For Each cell In Range("C2:C" & lRow) If IsDate(cell.Value) Then Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Copy Sheet4.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues 'Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Delete End If Next cell End If Next ws Sheet4.Select Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
The code selects each row of data in each sheet based on having a value (date) in Column C and summarises these rows in sheet4.
I'm not sure if you wanted the "used" data in each individual sheet removed once transferred to sheet4. This would be a good idea otherwise you'll end up with multiple duplicates in sheet4. In the above code, you'll see a line in green font (line 21). This is the line of code that will remove the "used" data from each individual sheet. It is currently de-activated but if you would like to activate this line of code, then simply remove the apostrophe at the begining of the line.
Following is a link to my test work book for you to peruse. Click on the button in sheet4 to see the code at work. You can actually execute the code from any sheet:-
https://www.dropbox.com/s/h73to6qkhl2ies1/Darlz%28Transfer%20from%20multi%20sheets%20to%20Summary%20sht.%29.xlsm?dl=0
I hope that this helps.
Cheerio,
vcoolio.