Copy & Paste two ranges from one sheet and merge to a single range on another sheet.

[Edgarvelez]

Hi, I have been using the code and all is good but my requirement changed a little.
Using the same code is it possible to copy range C & D from Sh1.
and paste them them merged to range E of sheet Sh3. with a / in between them
Looking for this result
7"x228" 6063GP Billet / MEDUI4510992

    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Set sh1 = Sheets("DashBoard")
    Set sh2 = Sheets("Sheet1")
    Set sh3 = Sheets("BOL")
    sh1.Range("D8").Copy
    sh3.Range("B8").PasteSpecial xlPasteFormulas
    sh1.Range("D9").Copy
    sh3.Range("E8").PasteSpecial xlPasteFormulas
    sh1.Range("D10").Copy
    sh3.Range("F8").PasteSpecial xlPasteFormulas
    sh1.Range("C12").Copy
    sh3.Range("E12").PasteSpecial xlPasteFormulas
    sh1.Range("C13").Copy
    sh3.Range("E13").PasteSpecial xlPasteFormulas
    sh1.Range("C14").Copy
    sh3.Range("E14").PasteSpecial xlPasteFormulas
    sh1.Range("C15").Copy
    sh3.Range("E15").PasteSpecial xlPasteFormulas
    sh1.Range("C15").Select
    Application.CutCopyMode = False
    sh1.Range("A7").Select

    


  Dim lr As Long

  lr = sh2.Range("F" & Rows.Count).End(3).Row
  sh2.Range("F3:F" & lr).Copy
  sh3.Range("B23").PasteSpecial xlPasteValues
  
  lr = sh2.Range("G" & Rows.Count).End(3).Row
  sh2.Range("G3:G" & lr).Copy
  sh3.Range("C23").PasteSpecial xlPasteValues
  
  
  lr = sh2.Range("C" & Rows.Count).End(3).Row
  sh2.Range("C3:C" & lr).Copy
  sh3.Range("E23").PasteSpecial xlPasteValues

  lr = sh2.Range("H" & Rows.Count).End(3).Row
  sh2.Range("H3:H" & lr).Copy
  sh3.Range("F23").PasteSpecial xlPasteValues
  
  
  
  lr = sh2.Range("B" & Rows.Count).End(3).Row
  sh2.Range("B3:B" & lr).Copy
  sh3.Range("G23").PasteSpecial xlPasteValues

End Sub

 

[HighAndWilder]

Hi, I have been using the code and all is good but my requirement changed a little.
Using the same code is it possible to copy range C & D from Sh1.
and paste them them merged to range E of sheet Sh3. with a / in between them
Looking for this result
7"x228" 6063GP Billet / MEDUI4510992

    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Set sh1 = Sheets("DashBoard")
    Set sh2 = Sheets("Sheet1")
    Set sh3 = Sheets("BOL")
    sh1.Range("D8").Copy
    sh3.Range("B8").PasteSpecial xlPasteFormulas
    sh1.Range("D9").Copy
    sh3.Range("E8").PasteSpecial xlPasteFormulas
    sh1.Range("D10").Copy
    sh3.Range("F8").PasteSpecial xlPasteFormulas
    sh1.Range("C12").Copy
    sh3.Range("E12").PasteSpecial xlPasteFormulas
    sh1.Range("C13").Copy
    sh3.Range("E13").PasteSpecial xlPasteFormulas
    sh1.Range("C14").Copy
    sh3.Range("E14").PasteSpecial xlPasteFormulas
    sh1.Range("C15").Copy
    sh3.Range("E15").PasteSpecial xlPasteFormulas
    sh1.Range("C15").Select
    Application.CutCopyMode = False
    sh1.Range("A7").Select

   


  Dim lr As Long

  lr = sh2.Range("F" & Rows.Count).End(3).Row
  sh2.Range("F3:F" & lr).Copy
  sh3.Range("B23").PasteSpecial xlPasteValues
 
  lr = sh2.Range("G" & Rows.Count).End(3).Row
  sh2.Range("G3:G" & lr).Copy
  sh3.Range("C23").PasteSpecial xlPasteValues
 
 
  lr = sh2.Range("C" & Rows.Count).End(3).Row
  sh2.Range("C3:C" & lr).Copy
  sh3.Range("E23").PasteSpecial xlPasteValues

  lr = sh2.Range("H" & Rows.Count).End(3).Row
  sh2.Range("H3:H" & lr).Copy
  sh3.Range("F23").PasteSpecial xlPasteValues
 
 
 
  lr = sh2.Range("B" & Rows.Count).End(3).Row
  sh2.Range("B3:B" & lr).Copy
  sh3.Range("G23").PasteSpecial xlPasteValues

End Sub

View attachment 65982

View attachment 65980

Try this.

    Worksheets("Sh3").Range("E2:E" & Worksheets("Sh1").Cells(Rows.Count, 3).End(xlUp).Row).Formula = "=Sh1!$C2 & " & Chr(34) & " / " & Chr(34) & " & Sh1!$D2"
    
    Worksheets("Sh3").Range("E2:E" & Worksheets("Sh1").Cells(Rows.Count, 3).End(xlUp).Row).Value = Worksheets("Sh3").Range("E2:E" & Worksheets("Sh1").Cells(Rows.Count, 3).End(xlUp).Row).Value

It will start the result in row 2.

 

[Edgarvelez]

Hi, so just to put these two lines of code to the end of what I have.

 

[Edgarvelez]

I got to the first line and

 

[HighAndWilder]

Hi, so just to put these two lines of code to the end of what I have.

What are your sheets called?

I assumed that they were Sh1 and Sh3.

Just change the references to sh1 and sh3 in my code to represent the sheet names.

 

[Edgarvelez]

Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Set sh1 = Sheets("DashBoard")
Set sh2 = Sheets("Sheet1")
Set sh3 = Sheets("BOL")

 

[Edgarvelez]

ok will give it a try and revert

 

[Edgarvelez]

I made the changes and it's just not working.

 

[Edgarvelez]

Perhaps I explained incorrectly and sorry for any confusion.
I have 3 sheets as follows:

    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Set sh1 = Sheets("DashBoard")
    Set sh2 = Sheets("Sheet1")
    Set sh3 = Sheets("BOL")

For this code that I need, we are going to be working with sh2 & sh3
In sh2 I need to copy the displayed ranges of C & D the range count changes (Dynamic).
I need to paste them in sh3 starting at E23 merged separated with a space forwards slash space " / "

In sh3 D23 should look like this: 7"x228" 6063GP Billet / MEDUI4510992 and so on for the range.

This is sh2

This is sh3

 

[Edgarvelez]

Correction
In sh3 E23 should look like this: 7"x228" 6063GP Billet / MEDUI4510992