Sleeper: Using a loop to set and copy a range

[jasonfoz]

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...

[geekgirlau]

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:

=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!

[jasonfoz]

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

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"

[gibbo1715]

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

Gibbo

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

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

[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!

[jasonfoz]

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.

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"