Combining columns of data

[zdodson]

Hello all,

I have four worksheets("Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5"). I have data in all of the A columns on each of these worksheets. These columns represent data for different departments.

I am needing to consolidate the data in all four columns into one running list of accounts, which will be in "Sheet 1", Column A.

Can somebody help me with the code on this?


Thanks,
Zack

 

[Rick Rothstein]

Assuming your data starts on Row 2 (with Row 1 being a header cell) on each sheet, something like this should work...

Sub CombineData()
  Dim WS As Worksheet, LastRow As Long
  For Each WS In Worksheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5"))
    WS.Range("A2:A" & WS.Cells(Rows.Count, "A").End(xlUp).Row).Copy _
       Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1)
  Next
End Sub

 

[zdodson]

I am assuming that I am putting that data behind the worksheet instead of the userform itself. Am I wrong here? Can you clarify?

 

[zdodson]

Nevermind, it was a syntax error on my part--didn't put the code in correctly the first time. Your code works perfectly! Thanks again, Rick!

 

[zdodson]

One more question, Rick. It produces a list that has duplicates of names. Is there a way to include code to remove those duplicates?

 

[Rick Rothstein]

One more question, Rick. It produces a list that has duplicates of names. Is there a way to include code to remove those duplicates?

You didn't say anything about removing duplicates and since you said you wanted to "consolidate the data in all four columns into one running list", it didn't seem like that would have been a concern. Given that you want to remove duplicates, would it be alright to sort your list so that the duplicates would group together and be easier to find? If sorting is not okay, then which of the duplicates would you want to keep (first, last, one of the possible middle ones)?

 

[zdodson]

You didn't say anything about removing duplicates and since you said you wanted to "consolidate the data in all four columns into one running list", it didn't seem like that would have been a concern. Given that you want to remove duplicates, would it be alright to sort your list so that the duplicates would group together and be easier to find? If sorting is not okay, then which of the duplicates would you want to keep (first, last, one of the possible middle ones)?

I figured it out. With the above example, I needed to record the macro performing the action of removing the duplicates (and keep them unsorted). Below is the code:

Sub CombineData()
  Dim WS As Worksheet, LastRow As Long
  For Each WS In Worksheets(Array("Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5"))
    WS.Range("A2:A" & WS.Cells(Rows.Count, "A").End(xlUp).Row).Copy _
       Worksheets("Sheet 1").Cells(Rows.Count, "J").End(xlUp).Offset(1)
  Next
  
   Sheets("Sheet 1").Select
    Columns("J:J").Select
    ActiveSheet.Range("$J:$J").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Thanks again for the help!

Zack

 

[Rick Rothstein]

ActiveSheet.Range("$J:$J").[B][COLOR=#a52a2a]RemoveDuplicates [/COLOR][/B]Columns:=1, Header:=xlYes

Just so you know, the RemoveDuplicates method was introduced in Excel 2007, so if anyone using your workbook is doing so in Excel 2003 or earlier, your code will error out for them.I

I am curious, though, if you are going to remove duplicates, why is the existing sort order important to maintain?