CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Marc
Marc

Reputation: 1

Copy a range using indirect

Looking to build a macro to copy and paste data. The column length of the data to copy from will keep changing so I'm trying to find a way to capture this data. I figured putting a formula directly into the sheet to put the range would be a good start, so I'm wondering how do I copy from a range that's located in my cell N1.

cell N1

Upvotes: 0

Views: 306

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

Hopefully this code will be a good start:

Sub CopyPaste()
    Dim rngCopy             As Excel.Range
    Dim rngPaste            As Excel.Range

    With Sheet1
        Set rngCopy = .Range(.Range("N1").Value)
        Set rngPaste = .Range("A1")
        rngCopy.Copy
        rngPaste.PasteSpecial (xlPasteAll)
    End With
End Sub

This code assumes that you are copying data from the range that you specified in cell N1 and pasting it into cell A1 (you can easily modify it by changing rngPaste parameter).

Let me know if you have any questions. Take care.

Upvotes: 1

Related Questions