Simple loop based on Defined Name

mwf05

New Member
Joined
Jun 19, 2006
Messages
7
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

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
I think this should do the trick...

Code:
Sub UpdateTable()
'
' UpdateTable Macro

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

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
You need to Dim "m" as a Range.
The code below should also work
Code:
[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

New Member
Joined
Jun 19, 2006
Messages
7
I think this should do the trick...

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

New Member
Joined
Jun 19, 2006
Messages
7
As with the other solution provided, I get the same error.
Run-time error of '1004': Application-defined or object-defined error.
 

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
I think this should do the trick...

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

New Member
Joined
Jun 19, 2006
Messages
7
First line of the code, starting with [B6]. I am using Excel 2010.
 

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
Try this...

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

Forum statistics

Threads
1,081,665
Messages
5,360,369
Members
400,583
Latest member
gabivleon

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top