Copy Worksheet plus ranges
 

Greetings,

I have a workbook that is growing in functionality. One sheet is named
Charley (for discussion). There is a range called Blocks that holds several
entries. First, I need to make one copy of Charlie for each entry in the
Blocks range. I would like the copy placed after Charlie and renamed to
reflect the concatenation of the Block name a space and Charlie. This would
be repeated for each block in the range. The code below generates Charlie(2)
and then gives me a run time error.

What am I doing wrong?

Thanks in advance for your help!

Ray


Sub Copy_Charlie()

Dim Block As Range
Dim SheetNam As String
Dim ModSheetNam As String
Dim ModSheetNam2 As String

SheetNam = "Charlie"
ModSheetNam = SheetNam

For Each Block In Range("Blocks")
ModSheetNam2 = Block.Value + " " + SheetNam

Worksheets(SheetNam).Copy(after:=Worksheets(ModShe etNam)).Name _
= ModSheetNam2

ModSheetNam = ModSheetNam2

Next
End Sub

Copy Worksheet plus ranges
 

Change this line:

Worksheets(SheetNam).Copy(after:=Worksheets(ModShe etNam)).Name _
= ModSheetNam2

to

Worksheets(SheetNam).Copy after:=Worksheets(ModSheetNam)
Activesheet.Name = ModSheetNam2


Regards
Rowan

"Ray Batig" wrote:

Greetings,

I have a workbook that is growing in functionality. One sheet is named
Charley (for discussion). There is a range called Blocks that holds several
entries. First, I need to make one copy of Charlie for each entry in the
Blocks range. I would like the copy placed after Charlie and renamed to
reflect the concatenation of the Block name a space and Charlie. This would
be repeated for each block in the range. The code below generates Charlie(2)
and then gives me a run time error.

What am I doing wrong?

Thanks in advance for your help!

Ray


Sub Copy_Charlie()

Dim Block As Range
Dim SheetNam As String
Dim ModSheetNam As String
Dim ModSheetNam2 As String

SheetNam = "Charlie"
ModSheetNam = SheetNam

For Each Block In Range("Blocks")
ModSheetNam2 = Block.Value + " " + SheetNam

Worksheets(SheetNam).Copy(after:=Worksheets(ModShe etNam)).Name _
= ModSheetNam2

ModSheetNam = ModSheetNam2

Next
End Sub

Copy Worksheet plus ranges
 

Works great! Looks like I was trying to do too many things in one step.

Thanks

Change this line:

Worksheets(SheetNam).Copy(after:=Worksheets(ModShe etNam)).Name _
= ModSheetNam2

to

Worksheets(SheetNam).Copy after:=Worksheets(ModSheetNam)
Activesheet.Name = ModSheetNam2


Regards
Rowan

"Ray Batig" wrote:

Greetings,

I have a workbook that is growing in functionality. One sheet is named
Charley (for discussion). There is a range called Blocks that holds
several
entries. First, I need to make one copy of Charlie for each entry in the
Blocks range. I would like the copy placed after Charlie and renamed to
reflect the concatenation of the Block name a space and Charlie. This
would
be repeated for each block in the range. The code below generates
Charlie(2)
and then gives me a run time error.

What am I doing wrong?

Thanks in advance for your help!

Ray


Sub Copy_Charlie()

Dim Block As Range
Dim SheetNam As String
Dim ModSheetNam As String
Dim ModSheetNam2 As String

SheetNam = "Charlie"
ModSheetNam = SheetNam

For Each Block In Range("Blocks")
ModSheetNam2 = Block.Value + " " + SheetNam

Worksheets(SheetNam).Copy(after:=Worksheets(ModShe etNam)).Name _
= ModSheetNam2

ModSheetNam = ModSheetNam2

Next
End Sub