Automatically Copy data from 3 sheets to another

[mj_bowen]

Hi,

I am trying to copy data from three separate tables (from sheet 1, 2 and 3) into 1 table (sheet 4). I do not want to combine the data, instead have all of the data in one place.

I have tried the consolidate function, but am not having much luck?

Please see attached example,

Regards,

Matt

https://www.box.com/s/63740d272f3b3eb84416

 

[Tinbendr]

I do not want to combine the data, instead have all of the data in one place.

I hope you mean all the data in one column.

Sub LineUp()
    Dim Sh As Worksheet
    Dim DestSheet As Worksheet
    Dim Rng As Range
    Dim LR1 As Long
    Dim LR2 As Long
    
    Set DestSheet = ActiveWorkbook.Worksheets("Sheet5")
    
    For Each Sh In Worksheets

        If Sh.Name <> "Sheet5" Then
            LR1 = LastRow(Sh)
            Sh.Range("a2:b" & LR1).Copy

            LR2 = LastRow(Worksheets("Sheet5"))
            DestSheet.Range("A" & LR2 + 1).PasteSpecial xlValues

        End If
    Next
    
    'Esthetics
    Application.CutCopyMode = False
    DestSheet.Range("A1").Select
End Sub

Function LastRow(Sh As Worksheet)
    On Error Resume Next
    LastRow = Sh.Cells.Find(What:="*", _
                            After:=Sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

 

[mj_bowen]

Hi David,

Thank you for your reply, as you may have guessed I'm a beginner at VBA! I should have been more specific, I would like the data to appear in two columns - just like the other tables on sheet 1, 2 and 3.

Please see this as an example of what I would like the data to look like - I have just copied the data manually from sheets 1, 2 and 3 into sheet 4.

Thank you again for your help.


https://www.box.com/s/87718c44c7d45b940758

 

[Tinbendr]

Yes, I assumed so, since your first example only showed the one column.

Go to editor. Click Insert->Module. Copy the code above and paste into module. Adjust sheet names as required. F5 to run from editor, or go back to sheet, Press F8, select LineUp.

You weren't specific about how many sheets, so I included all of them EXCEPT the sheet5. If you have other sheets that don't need to be included, we may have to use a different approach to cycle through the data sheets.

 

[mj_bowen]

Hi David,

Thank you for your message,

I inserted the above code but kept having this message:

Error:
LastRow = Sh.Cells.Find(What:="*", _
After:=Sh.Range("A1"), _

I could not seem to get the code to work?

In response to your last question - The ultimate aim of the worksheet will be to copy the data (both columns) of all 8 classes (Emerald, Gold, Sapphire, Ruby, Amber, Aqua, Crimson and Turquoise) to the 'All Classes' sheet. I have included an example. Thank you once again for your advice!

Example: https://www.box.com/s/58b2d9b9c67e93c843f7

Matt

 

[Tinbendr]

You had it in a sheet code page, not in a module. (Although I doubt it would have made a difference.) And you had a copy error in the function. I just u/l a complete worksheet.

 

[mj_bowen]

Thank you...you have really been fantastic and hopefully this will help lots of teachers this week!

Matt