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.
