Previous Topic Next Topic

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 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 12, 2016 at 07:14 PM
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 response

Answer 1 / 1
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Mar 12, 2016 at 07:14 PM
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.
0