CODEWITHSUNDEEP
vbaexcel
Yigit Tanverdi
Yigit Tanverdi

Reputation: 161

copying and pasting values in to same row in mastersheet

I have the following code, which copies and pastes columns from a worksheet called copySheet and pastes to sheet called pasteSheet to the first empty row. 

 lRow = copySheet.Cells(copySheet.Rows.Count, 1).End(xlUp).Row

  With copySheet.Range("BX2:BX" & lRow)
   pasteSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
 .Resize(.Rows.Count, .Columns.Count) = .Value
  End With

 'Determine last row of Column B in copySheet
  lRow = copySheet.Cells(copySheet.Rows.Count, 1).End(xlUp).Row

 With copySheet.Range("CC2:CC" & lRow)
 pasteSheet.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Resize(.Rows.Count, .Columns.Count) = .Value
 End With

Now i would like to copy column BB (starting from BB2) and AK (starting from AK2) from "copySheet" and paste it together to Column L in "pasteSheet". BB and AK values should have one blank between each other in same cell. Does anyone know how i can do this?

Upvotes: 0

Views: 63

Answers (1)

Dave
Dave

Reputation: 4356

It sounds like you want to get 2 cells, concatenate them together with a space, then set that value into another sheet? You can do this easily enough with a For loop:

For myLoop = 2 to lRow 
    ' recalc firstEmptyRow and increment to get first empty row
    firstEmptyRow = pasteSheet.Cells(pasteSheet.Rows.Count, "L").End(xlUp).Row + 1
    myVal = copySheet.Range("BB" & myLoop).Value & " " & _
    copySheet.Range("AK" & myLoop).Value
    pasteSheet.Range("L" & firstEmptyRow).value = myVal 
Next

I have assumed in this case that you want the value posted to the same row in pasteSheet that you copied from in copySheet; if not, you would have to change the location in the pasteSheet.Range line

Upvotes: 1

Related Questions