Copy adjacent Sheet and name from a list

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

Code is in sheet "Summary" module.
This works fine as far as adding a number of sheet and naming them from the list C9:Cn on sheet named "Summary". There are four names and I get four regular sheets with those names.

What I want to do is COPY the sheet next to "Summary" which is named "Main Swb" and name the copies from the list in the "Summary" sheet C9:Cn.

Sheet "Main Swb" has some 'bells and whistles' color formatting, a few formulas and some 'merged cells" (big frown here). I need to keep all the goodies intact when copied and renamed.

So, I wind up with four copies of "Main Swb" with four different names.

I've tried to Set nSht = Sheets("Main Swb").Copy After:=Sheets(Sheets.Count)
but it errors out.

Thanks,
Howard

Option Explicit

Sub CreateSheets()
Dim nSht As Worksheet
Dim r As Integer
r = 9
Do While Sheets("Summary").Cells(r, 3).Value <> ""
Set nSht = Sheets.Add
'Names for the new sheet are in C9:Cn of "Summary"
nSht.Name = Sheets("Summary").Cells(r, 3).Value
r = r + 1
Loop
End Sub
 
Hi Howard,

Am Sun, 7 Apr 2013 07:09:42 -0700 (PDT) schrieb Howard:
Code is in sheet "Summary" module.
This works fine as far as adding a number of sheet and naming them from the list C9:Cn on sheet named "Summary". There are four names and I get four regular sheets with those names.

What I want to do is COPY the sheet next to "Summary" which is named "Main Swb" and name the copies from the list in the "Summary" sheet C9:Cn.
Click to expand...

try:

Sub CreateSheets()
Dim LRow As Long
Dim rngC As Range

With Sheets("Summary")
LRow = .Cells(Rows.Count, 3).End(xlUp).Row
For Each rngC In .Range("C9:C" & LRow)
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = rngC
Next
End With
End Sub


Regards
Claus Busch
 
Hi Howard,

Am Sun, 7 Apr 2013 16:19:59 +0200 schrieb Claus Busch:

sorry, but I didn't read carefully.
Try:

Sub CreateSheets()
Dim LRow As Long
Dim rngC As Range

With Sheets("Summary")
LRow = .Cells(Rows.Count, 3).End(xlUp).Row
For Each rngC In .Range("C9:C" & LRow)
Sheets("Main Swb").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = rngC
Next
End With
End Sub


Regards
Claus Busch
 
Hi Howard,



Am Sun, 7 Apr 2013 16:19:59 +0200 schrieb Claus Busch:



sorry, but I didn't read carefully.

Try:



Sub CreateSheets()

Dim LRow As Long

Dim rngC As Range



With Sheets("Summary")

LRow = .Cells(Rows.Count, 3).End(xlUp).Row

For Each rngC In .Range("C9:C" & LRow)

Sheets("Main Swb").Copy After:=Sheets(Sheets.Count)

ActiveSheet.Name = rngC

Next

End With

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Thanks, Claus.

I tried you revised code and it copied several, say about 17, sheets named ("Main Swb12") and various other numbers and ignored the names listed in
For Each rngC In .Range("C9:C" & LRow).

I revised the code to this:

Sub CreateSheetsClaus()
'Dim LRow As Long
Dim rngC As Range

With Sheets("Summary")
'LRow = .Cells(Rows.Count, 3).End(xlUp).Row
For Each rngC In .Range("C9:C24")
Sheets("Main Swb").Copy After:=Sheets(Sheets.Count)
Next
End With
End Sub

And it produced the same thing.
Where Range("C9:C24") on "Summary" sheet has only four names and all other cells are blank. Puzzling.

Here is a link to the workbook I am working with if you get the time to investigate. Not sure what is messing things up.

https://www.dropbox.com/s/3letip961h2ti2v/TENDER SHEET Drop Box Version.xlsm

Thanks.
Howard



https://www.dropbox.com/s/3letip961h2ti2v/TENDER SHEET Drop Box Version.xlsm
 
Hi Howard,

Am Sun, 7 Apr 2013 08:47:17 -0700 (PDT) schrieb Howard:

I tried you revised code and it copied several, say about 17, sheets named ("Main Swb12") and various other numbers and ignored the names listed in
For Each rngC In .Range("C9:C" & LRow).
Click to expand...

two things:
1. the code should be placed in a standard module
2. I missed one line of code. The important line:
ActiveSheet.Name = rngC

look here for your workbook "TENDER SHEET":
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191


Regards
Claus Busch
 
I tried to have a look at your file but it was no longer there!

While certain that Claus provided an excellent solution, I offer a
version that doesn't loop each range on "Summary" for the names of the
copied sheets.

Sub CopyAndNameSheets()
Dim vNames, n&, lRow&
With Sheets("Summary")
lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
vNames = .Range("C1:C" & lRow)
End With
For n = LBound(vNames) To UBound(vNames)
Sheets("Main Swb").Copy after:=Sheets("Main Swb")
ActiveSheet.Name = vNames(n, 1)
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,

Am Sun, 07 Apr 2013 14:24:57 -0400 schrieb GS:
Sub CopyAndNameSheets()
Dim vNames, n&, lRow&
With Sheets("Summary")
lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
vNames = .Range("C1:C" & lRow)
End With
For n = LBound(vNames) To UBound(vNames)
Sheets("Main Swb").Copy after:=Sheets("Main Swb")
ActiveSheet.Name = vNames(n, 1)
Next 'n
End Sub
Click to expand...

you didn't know the structure of the table. So you have to change two
lines of your code:

lRow = 8 + WorksheetFunction.CountA([C9:C24])
vNames = .Range("C9:C" & lRow)


Regards
Claus Busch
 
I tried to have a look at your file but it was no longer there!



While certain that Claus provided an excellent solution, I offer a

version that doesn't loop each range on "Summary" for the names of the

copied sheets.



Sub CopyAndNameSheets()

Dim vNames, n&, lRow&

With Sheets("Summary")

lRow = .Cells(.Rows.Count, 3).End(xlUp).Row

vNames = .Range("C1:C" & lRow)

End With

For n = LBound(vNames) To UBound(vNames)

Sheets("Main Swb").Copy after:=Sheets("Main Swb")

ActiveSheet.Name = vNames(n, 1)

Next 'n

End Sub



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Hi Garry,

Always glad to see/hear your 'Double Eagle' worth of advice. (Where "Double Eagle" replaces "two cents")<g>

Here is a new link to a newer version that has the sheet to be copied, "Main Swb", hidden.

I made simple additions to Claus's code and it seems to work quite well.

https://www.dropbox.com/s/vwq5ao0a8x9bp76/TENDER SHEET UnHide_Copy_Hide Drop Box V1.xlsm

I would assume I could add the Unhide / Hide code lines to your version and get the same results.

Howard
 
Hi Garry,
Am Sun, 07 Apr 2013 14:24:57 -0400 schrieb GS:
Sub CopyAndNameSheets()
Dim vNames, n&, lRow&
With Sheets("Summary")
lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
vNames = .Range("C1:C" & lRow)
End With
For n = LBound(vNames) To UBound(vNames)
Sheets("Main Swb").Copy after:=Sheets("Main Swb")
ActiveSheet.Name = vNames(n, 1)
Next 'n
End Sub
Click to expand...

you didn't know the structure of the table. So you have to change two
lines of your code:

lRow = 8 + WorksheetFunction.CountA([C9:C24])
vNames = .Range("C9:C" & lRow)


Regards
Claus Busch
Click to expand...

Claus,
The code is just a general example of how to not read each cell
directly from sheet. Now that I've downloaded Howard's file I see that
it would need to be modified to work with the structure. In this case I
suggest naming the list under col heading "BREAKDOWN" as a dynamic
range, and use this to load vNames.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,
Always glad to see/hear your 'Double Eagle' worth of advice. (Where
"Double Eagle" replaces "two cents")<g>

Here is a new link to a newer version that has the sheet to be
copied, "Main Swb", hidden.

I made simple additions to Claus's code and it seems to work quite
well.

https://www.dropbox.com/s/vwq5ao0a8x9bp76/TENDER SHEET UnHide_Copy_Hide Drop Box V1.xlsm

I would assume I could add the Unhide / Hide code lines to your
version and get the same results.

Howard
Click to expand...

Thanks for the link! I got a look at your file and see my code doesn't
fit it exactly. See my reply to Claus for my comments regarding how I'd
approach the task...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Revised as per my suggested approach (and handles the hidden sheet)...

Sub CopySheetAndNameCopies()
Dim vNames, n&
vNames = Sheets("Summary").Range("BreakdownList")

Application.ScreenUpdating = False
Sheets("Main Swb").Visible = True

For n = LBound(vNames) To UBound(vNames)
Sheets("Main Swb").Copy after:=Sheets("Summary")
ActiveSheet.Name = vNames(n, 1)
Next 'n

Sheets("Main Swb").Visible = False
Application.ScreenUpdating = True
End Sub

...where Range("BreakdownList") is a dynamic Defined Name range with
local scope.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Revised as per my suggested approach (and handles the hidden sheet)...



Sub CopySheetAndNameCopies()

Dim vNames, n&

vNames = Sheets("Summary").Range("BreakdownList")



Application.ScreenUpdating = False

Sheets("Main Swb").Visible = True



For n = LBound(vNames) To UBound(vNames)

Sheets("Main Swb").Copy after:=Sheets("Summary")

ActiveSheet.Name = vNames(n, 1)

Next 'n



Sheets("Main Swb").Visible = False

Application.ScreenUpdating = True

End Sub
Click to expand...
..where Range("BreakdownList") is a dynamic Defined Name range with

local scope.

Garry
Click to expand...

Thanks,Garry.

I have never worked with hidden sheets before, never had a cause to do so. Not as daunting as I thought it might be.

I'll test fly your code which I know already will work and archive it along with Claus's.

Thanks again.

Howard
 
Ok! Let me know if you need help with the dynamic named range...



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

I struggled a bit but got an example from excel google where you use =OFFSET(...,etc...,0,)) which is basiclly range("C9:C32") and a counta of the same range. And the name BreakdownList.

It worked with your code. Is that a true dynamic named range? The way the sheet is set up those 24 rows are pretty much the extent of range's range.

Doesn't have room to grow to 75 rows say...

Howard
 
Hi Claus and Gary,

Turns out once these new sheets are copied, there is a need for formulas onthe Summary sheet to refer to certain cells of each new sheet and return values from each new sheet to Summary. I used four offset lines to get it done, while I have some vision of it all in one line...nothing comes to mind..

This works fine, but I would appreciate an assement from you pros.

Sub CreateSheetsClaus_UnHide_Copy_Hide()
Dim rngC As Range
Application.ScreenUpdating = False
Sheets("Main Swb").Visible = True
With Sheets("Summary")
For Each rngC In .Range("C9:C24")
If Len(rngC) > 0 Then
Sheets("Main Swb").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = rngC
rngC.Offset(0, 1).Formula = "='" & rngC & "'!G7"
rngC.Offset(0, 2).Formula = "='" & rngC & "'!H7"
rngC.Offset(0, 3).Formula = "='" & rngC & "'!I7"
rngC.Offset(0, 4).Formula = "='" & rngC & "'!J7"
End If
Next
End With
Sheets("Main Swb").Visible = False
Application.ScreenUpdating = True
End Sub

Thanks.
Howard
 
I struggled a bit but got an example from excel google where you use
=OFFSET(...,etc...,0,)) which is basiclly range("C9:C32") and a
counta of the same range. And the name BreakdownList.

It worked with your code. Is that a true dynamic named range? The
way the sheet is set up those 24 rows are pretty much the extent of
range's range.

Doesn't have room to grow to 75 rows say...

Howard
Click to expand...

Well no, that's not the correct way to make a dynamic range because it
includes blanks, which you do not want to process. Here's how I would
define it...

Select C8;
In the Namebox (left of the FormulaBar) type...
"summary!BrkDn_Hdr"
without the quotes;

Open the Name Manager dialog and edit "BreakdownList" RefersTo as
follows...

=OFFSET(BrkDn_Hdr,1,0,COUNTA($C$9:$C$32),1)

...making sure that "Summary" is specified in the Scope field. This will
show the address when typed in the Immediate Window of the VBE...

?range("BreakdownList").Address

...and press Enter to see "$C$9:$C$12", which is the area where the 4
entries under BREAKDOWN reside.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Howard,

Am Mon, 8 Apr 2013 06:43:10 -0700 (PDT) schrieb Howard:
rngC.Offset(0, 1).Formula = "='" & rngC & "'!G7"
rngC.Offset(0, 2).Formula = "='" & rngC & "'!H7"
rngC.Offset(0, 3).Formula = "='" & rngC & "'!I7"
rngC.Offset(0, 4).Formula = "='" & rngC & "'!J7"
Click to expand...

you can shorten it:
rngC.Offset(0, 1).Resize(1, 4).Formula = _
"='" & rngC & "'!G7"


Regards
Claus Busch
 
Hi Howard,



Am Mon, 8 Apr 2013 06:43:10 -0700 (PDT) schrieb Howard:









you can shorten it:

rngC.Offset(0, 1).Resize(1, 4).Formula = _

"='" & rngC & "'!G7"





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Thanks, Claus. As I said I envisioned a one liner but clearly beyond my Excel skills.

Funny though, I can read it and understand what its doing. That will be a good one to keep around and refer to.

Thanks again.

Howard
 
Well no, that's not the correct way to make a dynamic range because it

includes blanks, which you do not want to process. Here's how I would

define it...



Select C8;

In the Namebox (left of the FormulaBar) type...

"summary!BrkDn_Hdr"

without the quotes;



Open the Name Manager dialog and edit "BreakdownList" RefersTo as

follows...



=OFFSET(BrkDn_Hdr,1,0,COUNTA($C$9:$C$32),1)



..making sure that "Summary" is specified in the Scope field. This will

show the address when typed in the Immediate Window of the VBE...



?range("BreakdownList").Address



..and press Enter to see "$C$9:$C$12", which is the area where the 4

entries under BREAKDOWN reside.
Click to expand...

Okay, I'm on it. Might as well do it correctly, eh?

Thanks.
Howard
 
Back
Top