Simple loop based on Defined Name

[mwf05]

I am having a problem with a simple loop that I want to run x number of times based upon a defined name in a worksheet. My defined name is Clients=counta($A:$A)-9

I have a macro that I am trying to execute that will copy a range down a number of times based upon the value found in the defined name of Clients, as shown above. However, nothing I have tried works. Here is the macro/loop I have setup.

Sub UpdateTable()
'
' UpdateTable Macro
'
Dim l As Integer
Dim m As Integer
'
Set m = Worksheets("Summary").Range("Clients")
Range("B6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
For l = 1 To m
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next l
End Sub

Currently, Clients is equal to 7. When I replace m with 7 everything works fine. However, since the number of clients will vary, I want to be able to import the current client count which is the value in the defined name.

My experience level is, well moderate at best. Looked all over for simple answer and have not been able to find it

 

[cstimart]

I think this should do the trick...

Sub UpdateTable()
'
' UpdateTable Macro

[B6].Resize(, [B6].End(xlToRight)).Copy
ActiveCell.Offset(1).Resize([Clients]).PasteSpecial xlPasteFormulas
End Sub

 

[MickG]

You need to Dim "m" as a Range.
The code below should also work

[COLOR="Navy"]Sub[/COLOR] MG08May50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] m [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] m = Worksheets("Summary").Range("Clients")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B6"), Range("B6").End(xlToRight))
Rng.Resize(m, Rng.Columns.Count) = Rng.Value
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mwf05]

I think this should do the trick...

Sub UpdateTable()
'
' UpdateTable Macro

[B6].Resize(, [B6].End(xlToRight)).Copy
ActiveCell.Offset(1).Resize([Clients]).PasteSpecial xlPasteFormulas
End Sub

Tried the above, however, I get a Run-time error of '1004': Application-defined or object-defined error.

 

[mwf05]

As with the other solution provided, I get the same error.
Run-time error of '1004': Application-defined or object-defined error.

 

[cstimart]

I think this should do the trick...

Sub UpdateTable()
'
' UpdateTable Macro

[B6].Resize(, [B6].End(xlToRight)).Copy
ActiveCell.Offset(1).Resize([Clients]).PasteSpecial xlPasteFormulas
End Sub

Tried the above, however, I get a Run-time error of '1004': Application-defined or object-defined error.

Where does the code bomb? What version of Excel are you using?

 

[mwf05]

First line of the code, starting with [B6]. I am using Excel 2010.

 

[cstimart]

Try this...

Sub UpdateTable()
Dim LC As Long
    LC = Cells(6, Columns.Count).End(xlToLeft).Offset(, -1).Column

    [B6].Resize(, LC).Copy ActiveCell.Offset(1).Resize([Clients])
End Sub