Repeating code

[Geoff Varner]

MrExcel,

I have the following code I would like to repeat.

Sub CopyRange()
Range("Geoff_Jan").Copy Destination:=Range("Geoff_Jan1").Cells(1, 1)
End Sub

I know this is initially a simple command so how can I write this so it will repeat for multiple range names such as Geoff_Feb.... and even other users such as Nancy_Jan?

Thanks,
Geoff

 

[schielrn]

This would repeat Geoff using Jan through Dec:

Sub CopyRange()
for i = 1 to 12
Range("Geoff_" & format(i & "/1/09","mmm")).Copy Destination:=Range("Geoff_" & format(i & "/1/09","mmm") & "1").Cells(1, 1)
next i
End Sub

Hope that helps.

 

[Geoff Varner]

Thanks for the suggestion schielrn; however, I get an error when I run this. The error is Runtime error 1004. What should I do?

 

[Geoff Varner]

OK, I fixed the error but it only copied to the Jan_1 named range and didn't copy to the Feb etc. Any more help would be setllar!

 

[schielrn]

I updated the code from the original, maybe you copied it before I updated it? But another thing is do all of the range names exist through december?

Do all these named ranges exist:

<TABLE style="WIDTH: 160pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=213 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=109 height=20>Geoff_Jan</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 78pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=104>Geoff_Jan1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Feb</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Feb1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Mar</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Mar1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Apr</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Apr1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_May</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_May1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Jun</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Jun1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Jul</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Jul1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Aug</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Aug1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Sep</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Sep1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Oct</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Oct1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Nov</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Nov1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Geoff_Dec</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Geoff_Dec1</TD></TR></TBODY></TABLE>

 

[Geoff Varner]

OK that works perfectly!

I had copied too soon before the code was complete also I had to make sure all the named ranges were right to get rid of the error.

Can I repeat for different groups of named ranges? Such as Nancy_Jan to Nancy_Jan1? Your help has made my day a success already. Thank you so much.

 

[schielrn]

In the code you can change Geoff to Nancy or, if there are going to be a lot of them, you can put all the names in one sheet and loop through the names using that sheet if you would like?

How many different groups of named ranges are there? You could do somehting like:

Sub CopyRange()
for i = 1 to 12
Range("Geoff_" & format(i & "/1/09","mmm")).Copy Destination:=Range("Geoff_" & format(i & "/1/09","mmm") & "1").Cells(1, 1)
Range("Nancy_" & format(i & "/1/09","mmm")).Copy Destination:=Range("Nancy_" & format(i & "/1/09","mmm") & "1").Cells(1, 1)
next i
End Sub

But again if there are a lot then I would put them in a sheet and loop through the sheet of names?