CODEWITHSUNDEEP
vbaexcel
Elatesummer
Elatesummer

Reputation: 313

Trying to merge columns together in Excel

I appreciate there are a lot of similar questions out there but I've been searching various forums for three days now and not yet found anything which does what I need - so either I'm doing something very strange or my searching skills aren't up to scratch!

I'd really appreciate it if someone could let me know where I'm going wrong, or even link me to a solution which might help as I haven't managed to find one.

I currently have a spreadsheet with six worksheets. Worksheets 2-6 contain data on items which have been sold from different sources. Worksheet 1 currently contains four columns which populate the item data using a macro I've cobbled together into four separate columns. Worksheet 2 contains an 'itemlist' column into which I want to copy the data from each of the four columns in worksheet 2.

I hope this makes sense. At the moment, the code I have is below:

Sub UpdateList()
  'Clear the current ranges
  Range("PharmacyItems").Clear
  Range("PrelabelItems").Clear
  Range("RestockItems").Clear
  Range("TakehomeItems").Clear
  Range("FullItemList").Clear

  'Populate control with unique list
  Range("PharmacyFullList").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("PharmacyItems"), Unique:=True
  Range("PrelabelFullList").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("PrelabelItems"), Unique:=True
  Range("RestockFullList").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("RestockItems"), Unique:=True
  Range("TakehomeFullList").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("TakehomeItems"), Unique:=True

  'Combine the four ranges into one
  Range("UniqueLists!$A:$A, UniqueLists!$B:$B, UniqueLists!$C:$C, UniqueLists!$D:$D").Copy Sheets("Drug totals").Range("A2")
  'Sort the data
  Range("FullItemList").Sort Key1:=Range("FullItemList").Columns(1), Order1:=xlAscending, Orientation:=xlSortColumns, Header:=xlYes, SortMethod:=xlPinYin, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption2:=xlSortNormal
End Sub

In order to clarify the above, here is a definition of which range is which:

PharmacyItems, PrelabelItems, RestockItems and TakehomeItems: these are the individual lists which contain the unique items copied from each data worksheet. FullList: the fulllist of the above four are the source data lists which are not unique lists FullItemList: the column into which I want all the data from the unique lists to end up

The reason I have a range which specifies each column rather than using a named range is that I was trying to see if this would make it any better as originally it was just giving me a vague and fluffy range issue error. With the columns defined in the range it tells me the size/shape of the destination doesn't match the source.

The exact error is: Run-time error '1004': The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following: - click a single cell, and then paste - select a rectangle that's the same size and shape, and then paste

Can anyone help me? Sadly I'm a SQL Server girl, I'd far rather be pulling data from a database but I'm not allowed on this one!

Thank you in advance

Summer

Upvotes: 0

Views: 532

Answers (2)

Tim Williams
Tim Williams

Reputation: 166980

If you don't need the individual unique lists but just want to create a single column of all the unique values then this should work for you (untested though...)

Sub UpdateList()

   Range("FullItemList").Clear  'Clear the full item list range

   'Populate control with unique list
   UniquesToFullItemList Range("PharmacyFullList")
   UniquesToFullItemList Range("PrelabelFullList")
   UniquesToFullItemList Range("RestockFullList")
   UniquesToFullItemList Range("TakehomeFullList")

   'Sort the data
   Range("FullItemList").Sort Key1:=Range("FullItemList").Columns(1), _
      Order1:=xlAscending, Orientation:=xlSortColumns, Header:=xlYes, _
      SortMethod:=xlPinYin, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
      DataOption2:=xlSortNormal

End Sub

Sub UniquesToFullItemList(rngFrom As Range)
   rngFrom.AdvancedFilter Action:=xlFilterCopy, _
     CopyToRange:=Sheets("Drug totals").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0), _
             Unique:=True
End Sub

Upvotes: 0

Jean-François Corbett
Jean-François Corbett

Reputation: 38551

You can't paste an entire column to a range starting on row 2 (or any other row than 1) because then the last row(s) of the column won't fit on the sheet. That's why Excel says "the Copy area and the paste area are not the same size".

Instead of 

Range("UniqueLists!$A:$A, UniqueLists!$B:$B, UniqueLists!$C:$C, UniqueLists!$D:$D").Copy Sheets("Drug totals").Range("A2")

try pasting it starting on the first row. 

Range("UniqueLists!A:D").Copy Sheets("Drug totals").Range("A1")

But I'm guessing you don't have data all the way down to the very bottom of your "UniqueLists" sheet? If so, then why are you copying the entire column? Just copy the part you need. Then you'll be able to paste starting on cell "A2". Example:

Range("UniqueLists!A1:D1234").Copy Sheets("Drug totals").Range("A2")

Upvotes: 4

Related Questions