Automatically Copy data from 3 sheets to another

mj_bowen

Board Regular
Joined
Oct 4, 2009
Messages
99
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Thank you...you have really been fantastic and hopefully this will help lots of teachers this week!

Matt
 
Upvote 0

Forum statistics

Threads
1,211,772
Messages
6,103,881
Members
447,883
Latest member
bennazi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top