0

I'm trying to copy the border format of range AV3: BE3 to the dynamic rows below. However, the code is copying the format till the 3,000th row and it's not stopping at the last row.

This is the code:

    Dim my_range_AVtoBE As Range
    Dim last_row As Long

   'Define Dynamic Range Accoring to Column B
    last_row = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

    Set my_range_AVtoBE = ActiveSheet.Range("AV3:BE3" & last_row)
    Range("AV3:BE3").Select
    Selection.Copy
    my_range_AVtoBE.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:=xlNone
    my_range_AV.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
Romio Rodriguez
  • 79
  • 7
  • 1
    don't use `ActiveSheet`. Explicitly set the sheet you want to work with to a variable and use that (or refer to the sheet directly). `Worksheets("Sheet1")`, for example. The *ActiveSheet* may not be what you think it is when the code is run. – Scott Holtzman Oct 28 '20 at 13:37
  • 4
    `Range("AV3:BE" & last_row)` - remove the second 3. – SJR Oct 28 '20 at 13:37
  • 1
    In addition to the answer from @SJR above, see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) – Profex Oct 28 '20 at 14:05

0 Answers0