0

I'm trying to write a macro which selects a range of cells to copy and paste values over. The range of cells selected will vary depending on the "RowOff" value. But the starting cell will always be Z12.

I was trying to use the offset function but the macro only seems to be selecting one cell. I think it might be to do with the range but I am not sure how to amend my code.

How do i fix it?

Thanks

Sub FixFOT()

    Dim RowOff As String
    RowOff = Sheet29.Range("C5").Value

    Sheet10.Select
    Range("Z12").Offset(165, RowOff).Copy
    Range("Z12").Offset(165, RowOff).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
theredindian
  • 27
  • 1
  • 7
  • 2
    Normally you don't need to `Select`. And I think you're looking for `Resize`. Also - first argument is rows, second is columns. And no need to copy/paste. Just set the `.Value` of the `Range` in question equal to itself. – BigBen Sep 26 '19 at 13:15
  • It might help to read [How to avoid using Select in Excel VBA?](https://stackoverflow.com/q/10714251/4996248) – John Coleman Sep 26 '19 at 13:16
  • 1
    If the starting cell is always `Z12`, what about `Range(Cells(12,26),Cells(165,RowOff)).Copy`? – Foxfire And Burns And Burns Sep 26 '19 at 13:26
  • This is just an exampled based on what @BigBen pointed out: `Range("Z12").Resize(RowOff, 1).Value = Range("Z12").Resize(RowOff, 1).Value`. I've used only 1 for columns resize, as I'm not sure how many columns you want resize... – FAB Sep 26 '19 at 13:28
  • 1
    @BigBen thanks, using resize worked – theredindian Sep 26 '19 at 14:18
  • You don't need to use copy/paste, offset, or resize. Just set your Range as a variable and use `rng.Value = rng.Value` – GMalc Sep 26 '19 at 14:37

0 Answers0