Using a loop to set and copy a range

[malik641]

jasonfoz,
Just so you know, I placed your code in VB tags

I think I have a different answer for your problem, check it out:

Option Explicit

Sub DynamicRange()
    Dim NamedRange  As Range
    Dim iLastCol    As Long
    Dim activeRows  As Long
    Dim MostRows    As Long
    Dim iCol As Long
    iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    MostRows = 0
    For iCol = 1 To iLastCol
         activeRows = Cells(Rows.Count, iCol).End(xlUp).Row
         If activeRows > MostRows Then MostRows = activeRows
    Next iCol
    Set NamedRange = Range("A1", Cells(MostRows, iLastCol))
    ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=NamedRange
    ActiveSheet.Range("MyRange").Copy Sheets("Sheet2").Range("A1")
End Sub

This makes a defined name for you (Referring to Sheet1 - or whatever active sheet) and copies it to sheet 2...No matter how many different rows/columns.

Hope this helps!