Using a loop to set and copy a range

Hi All;

Hoping someone could help me out with a bit of code...

I have a range from Column A to Column AC but the number of rows changes all the time. I need to copy this range to another excel spreadsheet and have been trying to do this by setting a range name to "MyTable", and then copy it across. I have been trying to use a loop to do this with no success but I fear this is through my lack of understanding when it comes to loops!

Also, When I copy the active workbook across to the other excel s/sheet I need to turn off the warning that says I am replacing the file.

Any help would be greatly appreciated...

You can create a range that automatically adjusts the number of rows. If you select Insert | Name | Define, give the range a name and then set Refers To as the following:

Code:

---

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),29)

---

There are a couple of things to note with this method:

1. It will add quite a load to your spreadsheet if you have lots of this type of range name, so use it judiciously.
2. You won't see the range name listed in the Names drop-down list (to the left of the address bar) but you can still select it via code without any trouble.
3. The COUNTA (which is used to count the number of rows) needs to be a row that is always populated in your data.

Hope this gets you started. Please post your code if you want some further help with this so we can see where you're at!

This is the loop I am trying but got stuck - # of rows keep on changing.

Code:

---

Dim RowCount As Integer
RowCount = 1
Worksheets("Paymentsheet").Select
While Cells(1, RowCount).Value <> ""
    RowCount = RowCount + 1
Wend
If Cells(1, RowCount).Value = "" Then
    RowCount = RowCount - 1
    Row(RowCount).Select
    ' I would like to select this row and all rows above it up to column AC
End If

---

So I want to select all the rows with a value, up to column "AC" and name the table "MyTable"

Also, any idea how to turn off a warning in Excel that asks "Are you sure you want to Replace Existing File"

This is another method I use to create a dynamic range, If its of any use to you

Gibbo

Code:

---

Sub DynamicRange()
    Dim iLastRow As Long
    iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    ActiveSheet.Range("A1:AC" & iLastRow).Name = "MyTable"
End Sub

---

Same principle to copy from one sheet to another

Code:

---

Sub DynamicRange()
    Dim iLastRow As Long
    iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    ' Or sheet name if not active sheet!!!!!!!!!!
    ActiveSheet.Range("A1:F" & iLastRow).Copy Sheets("Sheet2").Range("A1")
End Sub

---

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

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

Code:

---

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!

Thanks Gibbo and Joseph;

I will give this a try a little later but in the meantime found a work around that seems to have done the job, although I think your coding looks a lot neater. If it does indeed work I may be replacing my code...

This is the code I eventually used and it seems to have worked... Thanks again for coming back to me.

Code:

---

With Worksheets("Paysheet")
    ec = 0
    ' find the last column, and name each column on the way
    Do Until Cells(1, 1).Offset(0, ec).Text = ""
        lastColumn = ec
        Set rangeToName = .Range(Cells(1, 1).Offset(0, ec), Cells(2, 1).Offset(0, ec))
        rangeToName.CreateNames Top:=True
        ec = ec + 1
    Loop
    Dim RowCounts As Integer
    RowCounts = 1
    With Worksheets("Paysheet")
        While Cells(RowCounts, 1).Value <> ""
            RowCounts = RowCounts + 1
        Wend
        RowCounts = RowCounts - 1
    End With
    Range(Cells(1, 1), Cells(1, 1).Offset(RowCounts - 1, ec - 1)).Select
    ActiveSheet.Range(Cells(1, 1), Cells(1, 1).Offset(RowCounts - 1, ec - 1)).Name = "MyTable"

---