We help IT Professionals succeed at work.

Copy dynamic range in excel

I need to copy a completely dynamic range in excel.  Meaning the rows and columns will be different each time.  Please note that the columns will change so I can't use something like range("M" & rowstart & ":" & "O" & rowend).copy.  I've seen a lot of examples like this but the columns are static and I need them to be able to change.

Here is what I'm currently using but I get an error:
MySheet.Range(MySheet.Cells(FirstCopyRow, columnpointer1), MySheet.Cells(RowPointer1 - 1, columnpointer1 + 6)).Copy
MyNewSheet.cells(Rowpointer2,2).pastespecial

This doesn't work.

Thanks!
Comment
Watch Question

Author

Commented:
Ok I think I figured it out.  

I needed to set it to the range first.  Like this:

    Set Rng = MySheet.Range(MySheet.Cells(FirstCopyRow, columnpointer1), MySheet.Cells(RowPointer1 - 1, columnpointer1 + ColCopyCount))
    Rng.Copy

Author

Commented:
hmm I'm still getting an error so maybe I didn't figure it out
Ok this time I really got it figured out.  This whole post has just been me talking to myself haha

Here is the solution:

MySheet.Range(MySheet.Cells(FirstCopyRow, ColPointer1), MySheet.Cells(RowPointer1 - 1, ColPointer1 + 6)).Copy
NewSheet.Cells(RowPointer2, 2).PasteSpecial
mattlaltman,

I suggest you 'Accept' one of your comments as the solution - so closing this question.

Patrick