Copy a row format from one worksheet to another

Question

I'm trying to copy a row format from one worksheet to another.

This code works for every row other than row 8. It doesn't bug out, it just doesn't copy and paste the row.

Lr = Sheets("34.5kV Cable Schedule").Range("K" & Rows.Count).End(xlUp).Row

Sheets("Format").Range("A4:R4").Copy
Rows(Lr + 1).PasteSpecial
Application.CutCopyMode = False
Range("CounterInCable").Value = Range("CounterInCable").Value + 1
Rows(Lr + 1).Select
Range("E" & (ActiveCell.Row)).Select
ActiveCell.Value = Range("CounterInCable").Value

Lr is used to know where the last pasted row is so that this pastes below it
Counter is a counter
Last two lines make that particular cell = the counter

If I add 1 to Lr it works for row 9 and after.

Since the Lr counter is based on End(xlUp), maybe the row which it finds last is empty? So it doesn't go up as intended? Presumably that's the row you pasted before row 9? — SnowGroomer

Josh Bullough Josh Bullough · Accepted Answer · 2020-07-24T21:07:48

   Sub tryThis()
   ''this skips line 1 and pastes every line down from it. Incase you have a header 
   ''row.

   lr = Sheets("34.5kV Cable Schedule").Range("K" & Rows.Count).End(xlUp).Row
   Sheets("Format").Range("A4:R4").Copy
   Rows(lr + 1).PasteSpecial
   Application.CutCopyMode = False
   Rows(lr + 1).Select

   End Sub

or you can try this

   Sub tryThisSecond()
   ''this will paste on every row.

   lr = Sheets("34.5kV Cable Schedule").Range("K" & Rows.Count).End(xlUp).Row
   Sheets("Format").Range("A4:R4").Copy
   Rows(lr).PasteSpecial
   Rows(lr).Copy
   Rows(lr + 1).PasteSpecial


   End Sub

Copy a row format from one worksheet to another

1 Answers