Combine values in two cells into one on seprate worksheet

[ktjf]

I have a workbook with two worksheets in it. In one worksheet called Materials List I have multiple rows that when selected, automate into the other worksheet named BOM. In the Materials List tab, I need columns H and I to combine (not sum, just combine descriptions) into column C in the BOM tab. You can see in the code where I just typed in H+I in one spot... and C in the respective spot. I don't want to erase any of my other code though! HELP PLEASE! and thanks in advance!

Sub CopyData()

  Dim Cell As Range
  Dim DstWks As Worksheet
  Dim R As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim SrcCols() As Variant
  Dim SrcWks As Worksheet
  

   'Name of the destination worksheet
    Set DstWks = ThisWorkbook.Worksheets("BOM")
    
   'Name of the source data worksheet
    Set SrcWks = ThisWorkbook.Worksheets("Material List")
    
   'Set search range to start at E1 - Change this if you need to.
    Set Rng = SrcWks.Range("A6")
    Set RngEnd = SrcWks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row > Rng.Row, SrcWks.Range(Rng, RngEnd), Rng)
    
     'Next available row on the destination worksheet.
      Set RngEnd = DstWks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False)
      If RngEnd Is Nothing Then
         A = 2
      Else
        A = RngEnd.Row + 1
      End If
      
     'Data columns on source worksheet to copy.
      SrcCols = Array("G", "H+I", "I", "J", "K", "L", "O", "M", "N")

      For Each Cell In Rng
        If Cell = "y" Then
           R = Cell.Row
             DstWks.Cells(A, "A") = SrcWks.Cells(R, SrcCols(0))
             DstWks.Cells(A, "C") = SrcWks.Cells(R, SrcCols(1))
             DstWks.Cells(A, "B") = SrcWks.Cells(R, SrcCols(2))
             DstWks.Cells(A, "D") = SrcWks.Cells(R, SrcCols(3))
             DstWks.Cells(A, "E") = SrcWks.Cells(R, SrcCols(4))
             DstWks.Cells(A, "F") = SrcWks.Cells(R, SrcCols(5))
             DstWks.Cells(A, "I") = SrcWks.Cells(R, SrcCols(6))
             DstWks.Cells(A, "N") = SrcWks.Cells(R, SrcCols(7))
             DstWks.Cells(A, "T") = SrcWks.Cells(R, SrcCols(8))
           A = A + 1
        End If
      Next Cell
      
End Sub

 

[JLGWhiz]

You will not be able to copy the two cell values in that manner. You have options to put them in a variable by concantenating them var = "a1" & "b1" and then making the destination range equal to the variable "c1" = var. Or make direct value assignment "c1" = "a1" & "b1". You cannot use the above as code, it is only to illustrate technique.

 

[ktjf]

Re: Combine values in two cells into one on separate worksheet

Thanks for your quick reply!
Yea, I knew that what I typed in wasn't going to work, I just put them there as "placeholders". Originally I had the two different columns automating into separate "destination columns" but now I'm being asked to combine them together.

When you said...

You have options to put them in a variable by concantenating them var = "a1" & "b1" and then making the destination range equal to the variable "c1" = var. Or make direct value assignment "c1" = "a1" & "b1"

Would I insert these into my DstWks.Cells(A, "A") = SrcWks.Cells(R, SrcCols(0)) type code or would this happen before/after?

 

[JLGWhiz]

I suppose you could modify your For Each statement as follows and get what you want:

Code:
For Each cell In rng
If cell = "y" Then
r = cell.Row
DstWks.Cells(a, "A") = SrcWks.Cells(r, SrcCols(0))
DstWks.Cells(a, "C") = SrcWks.Cells(r, SrcCols(1)) & SrcWks.Cells(r, SrcCols(2))
DstWks.Cells(a, "B") = SrcWks.Cells(r, SrcCols(2))
DstWks.Cells(a, "D") = SrcWks.Cells(r, SrcCols(3))
DstWks.Cells(a, "E") = SrcWks.Cells(r, SrcCols(4))
DstWks.Cells(a, "F") = SrcWks.Cells(r, SrcCols(5))
DstWks.Cells(a, "I") = SrcWks.Cells(r, SrcCols(6))
DstWks.Cells(a, "N") = SrcWks.Cells(r, SrcCols(7))
DstWks.Cells(a, "T") = SrcWks.Cells(r, SrcCols(8))
a = a + 1
End If
Next cell
Code:

 

[ktjf]

That works perfectly!
Thanks!
Only thing is, the two columns are merged without a space between them... is there a way to add a space or even a semi colon?
Thanks again.

 

[JLGWhiz]

This should work.

Code:
DstWks.Cells(a, "C") = SrcWks.Cells(r, SrcCols(1)) & " " & SrcWks.Cells(r, SrcCols(2))

There are two spaces between the quotation marks.

 

[ktjf]

Thanks a million!