VBA to copy each row from multiple sheets to single sheet

Ulysses31

New Member
Joined
Sep 14, 2011
Messages
4
I've been looking around, but without knowing the technical term for what I'm attempting I've struggled to find a solution.
here's an example.

Sheet 1
column a
column bcolumn ccolumn dcolumn e
00emp1234529012019A12345
00emp1234629012019A12346

<tbody>
</tbody>

Sheet 2
column a
column bcolumn ccolumn dcolumn e
10post12345LondonA12345
10post12346ManchesterA12346

<tbody>
</tbody>

Sheet 3
column acolumn bcolumn ccolumn dcolumn e
15person12345john doeM
15person12346jane smithF

<tbody>
</tbody>

i need to combine them row by row but grouped by a common column (in this case c - a unique ID)
eg
sheet 4
column acolumn bcolumn ccolumn dcolumn e
00emp1234529012019a12345
10post12345London
a12345
15person12345John DoeM
00emp1234629012019a12346
10post12346Manchestera12346
15person12346Jane SmithF

<tbody>
</tbody>

this macro needs to loop until it reaches an empty row in sheet 1.

any suggestions as to what i'm attempting?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Why not just copy/paste the data from each sheet1-3 to sheet4 then sort on col C?
 
Upvote 0
that would be the sensible way wouldn't it. but none of the columns have headers. so when sorting, it'll sort column C but the others would remain in place?
 
Upvote 0
Try this for results on sheet4.
I have assumed that there are no Headers.
Code:
[COLOR="Navy"]Sub[/COLOR] MG15Feb52
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Shts [COLOR="Navy"]As[/COLOR] Variant, S [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Q [COLOR="Navy"]As[/COLOR] Variant, Ray [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

Shts = Array("Sheet1", "Sheet2", "Sheet3")
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] S [COLOR="Navy"]In[/COLOR] Shts
[COLOR="Navy"]With[/COLOR] Sheets(S)
    Ray = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Resize(, 5)
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Ray(n, 3)) [COLOR="Navy"]Then[/COLOR]
        ReDim nray(1 To 1000, 1 To 5)
        [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
            nray(1, Ac) = Ray(n, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
        Dic.Add Ray(n, 3), Array(1, nray)
    [COLOR="Navy"]Else[/COLOR]
        Q = Dic(Ray(n, 3))
         Q(0) = Q(0) + 1
         [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
            Q(1)(Q(0), Ac) = Ray(n, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
       Dic(Ray(n, 3)) = Q
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] S


ReDim rray(1 To 10000, 1 To 5)
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
c = 0
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
    [COLOR="Navy"]For[/COLOR] Rw = 1 To Dic(K)(0)
        c = c + 1
        [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
            rray(c, Ac) = Dic(K)(1)(Rw, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]Next[/COLOR] K

[COLOR="Navy"]With[/COLOR] Sheets("Sheet4").Range("A1").Resize(c, 5)
    .Value = rray
    .Columns.AutoFit
    .Borders.Weight = 2
    .HorizontalAlignment = xlCenter
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
that would be the sensible way wouldn't it. but none of the columns have headers. so when sorting, it'll sort column C but the others would remain in place?
No, the others will not remain in place. When you're ready to sort, go to Data>Sort & Filter> Sort and just uncheck the box that says 'My data has headers' and sort on col C.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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