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]

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

Do you want the Totals row to be moved down or up as appropriate?

What are you to do about the other columns in the BOL worksheet. Do you want the new totals to be entered?

 

[Edgarvelez]

Do you want the Totals row to be moved down or up as appropriate?

What are you to do about the other columns in the BOL worksheet. Do you want the new totals to be entered?

View attachment 66011

The totals are a formula that is on the sheet and the other columns I already have the code for that part so no worries, all that I need is the description part starting on Cell E23.

 

[HighAndWilder]

So you want rows to be inserted to accommodate the data from the Dashboad?

 

[Edgarvelez]

Dashboard?
I need sh2 (Sheet1) to sh3 (BOL) downwards please

 

[HighAndWilder]

You have set sh1 as the Dashboard worksheet but the image shows that the source data is on Sheet1.
Set sh1 = Sheets("DashBoard")
Set sh2 = Sheets("Sheet1")
Set sh3 = Sheets("BOL")

Call the following procedure using this line.

Call subCopyDescription

It will insert the appropriate number of rows immediately above the totals line.

It will ignore blank rows that may be above the totals row.

Private Sub subCopyDescription()
Dim rngCell As Range
Dim Ws As Worksheet
Dim rng As Range
Dim lngLastRow As Long

    Set Ws = Worksheets("Sheet1")
    
    lngLastRow = Ws.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Set rng = Worksheets("BOL").Range("E22:E" & lngLastRow).Find("TOTAL", LookIn:=xlValues)
        
    If rng Is Nothing Then
        Exit Sub
    End If
   
    For Each rngCell In Ws.Range("C2:C" & Ws.Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        
        rng.EntireRow.Insert
        
        With rng.Offset(-1, 0)
            .Value = rngCell.Value & " \ " & rngCell.Offset(0, 1).Value
            .Font.Bold = False
        End With
            
    Next rngCell

End Sub

 

[Edgarvelez]

Hi as you can see I have pasted it exactly the same way and it goes as far as Exit Sub which is highlighted and finishes and does not go on to reading the rest of the code.
Let me know what changes I need to make and thanks.

I copied and pasted exactly

 

[HighAndWilder]

Change the word 'TOTAL' to 'Totals' if that is what you have in the last row in column E.

 

[Edgarvelez]

I changed it on the sheet to match exactly the code and it does the same.

 

[HighAndWilder]

What row is the Totals row in the BOLS worksheet?

 

[Edgarvelez]

E36