Transfer data from multiple sheets to one sheet.

[Closed]
Report
Posts
1
Registration date
Friday March 11, 2016
Status
Member
Last seen
March 12, 2016
-
Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021
-
Hi all, I'm kind of new to spreadsheets. I have 3 sheets each with the same columns. Column 1 has a type and column 2 has serial numbers, column 3 has the date it goes away for repairs. This repeats in all 3 sheet with different brands but when the units have a date that its off for repairs, how do I automatically transfer all the data from that row to sheet 4?

1 reply

Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021
233
Hello Darlz,

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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!