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