Excel VBA Copying Worksheet to Multiple Sheets 1

[rss01]

I have the following code that creates a new worksheet for each store in a range and copies data into each sheet. Everything works ok except I loose all my page setup I have on the master sheet. I brainlocking on what to change to copy the whole sheet, formating and all.

Public Sub StoreData()

Dim c As Range
Dim ws As Worksheet
Dim strWrkName As String

For Each c In Range("StoreList&quot
If WksExists(c.Value) = False Then
Set ws = Sheets.Add
ws.Name = c.Value
ws.Move After:=Sheets(Sheets.Count)
End If
strWrkName = c.Value

Worksheets("FOR&quot.Range("A1:H62&quot.Copy _
Destination:=Worksheets(strWrkName).Range("A1&quot

Sheets(strWrkName).Range("B3&quot.Value = c.Value

Next
MsgBox "Missing Store Sheets have Been Created and Data Updated"
End Sub

Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

 

[combo]

You need a copy of formatted sheet ("TempSheet&quot rather than create a new one:

[tt]Sheets("TempSheet&quot.Copy After:=Sheets(Sheets.Count)
Set ws=Sheets(Sheets.Count)
ws.Visible=True ' if "TempSheet" was hidden[/tt]

combo

 

[rss01]

Do I replace this code

Set ws = Sheets.Add
ws.Name = c.Value
ws.Move After:=Sheets(Sheets.Count)

With what you had?

My "TempSheet" is the worksheet "FOR"

 

[combo]

Yes if you need a full copy of worksheet "FOR". If no, I would rather make a copy of it, remove all unnecessary data rename and hide this sheet. Next use this copy (code is the same).

combo

 

[rss01]

when I put in your code it tries and make a complete new workbook. I want to just insert the new sheets in the existing workbook.

What am I doing wrong??

 

[RobBroekhuis]

Sounds like you don't have the "After:=" bit in your code. Could you list the code you have now?


Rob

 

[rss01]

Public Sub StoreData()

Dim c As Range
Dim ws As Worksheet
Dim strWrkName As String

For Each c In Range("StoreList&quot
If WksExists(c.Value) = False Then
Set ws = Sheets("FOR&quot.Copy
ws.Name = c.Value
ws.Move After:=Sheets(Sheets.Count)
End If
strWrkName = c.Value


Sheets(strWrkName).Range("B3&quot.Value = c.Value

Next
MsgBox "Missing Store Sheets have Been Created and Data Updated"
End Sub

Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

 

[RobBroekhuis]

The first line below

Set ws = Sheets("FOR&quot.Copy
ws.Name = c.Value
ws.Move After:=Sheets(Sheets.Count)

creates the new worksheet in a new workbook (and then tries to move it within that new workbook). You want, instead:

Set ws = Sheets("FOR&quot.Copy After:=Sheets(Sheets.Count)
ws.Name = c.Value



Rob

 

[rss01]

I get a compile error Syntax error when I put it in???

 

[RobBroekhuis]

Ah yes, it should be

Set ws = Sheets("FOR&quot.Copy (After:=Sheets(Sheets.Count))

since you are using the method to return a value.


Rob

 

[rss01]

Rob,

Thanks the code is copying the sheet but its not changing the name of the sheet to the ws.Name = c.Value

It makes a sheet and names it "FOR (2)" then I get an error "Runtime error 424 - Object required" on the line of code Set ws = Sheets("FOR&quot.Copy (After:=Sheets(Sheets.Count))

This is what I have in there now.

Public Sub StoreData()

Dim c As Range
Dim ws As Worksheet
Dim strWrkName As String

For Each c In Range("StoreList&quot
If WksExists(c.Value) = False Then

Set ws = Sheets("FOR&quot.Copy(After:=Sheets(Sheets.Count))
ws.Name = c.Value

End If
strWrkName = c.Value

Sheets(strWrkName).Range("B3&quot.Value = c.Value

Next
MsgBox "Missing Store Sheets have Been Created and Data Updated"
End Sub

Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

 

[RobBroekhuis]

OK. I think I was wrong - the copy method doesn't return a sheet object. Instead, just say:

Sheets("FOR&quot.Copy After:=Sheets(Sheets.Count)
activesheet.Name = c.Value



Rob

 

[rss01]

Thank you very much...That was it.

Something so simple

 

[rss01]

Ron,

I just wanted to extend an extra Thanks for taking the time to help me with this. It seems like sometimes the "EXPERTS" in the forums only want to get involved with the the postings that are beyond the normal.

Hopefully they will remember that at sometime they where just like me, trying to only accomplish the things that people who make more money then them are asking.

Sometimes the easiest things are the hardest.

Again, Thanks for taking time to help me understand and learn at the same time.

 

[RobBroekhuis]

Rss,
I'm glad to help. But I have to counter your impression of "elitist experts": each of the top 4 "experts" on this forum regularly answers both advanced and elementary questions. It's just that once one person starts extending his/her help, the others usually go to the next thread to help out, so as to not spread ourselves too thin. I think we're all eager to help around here, no matter what the difficulty level of the question. As a matter of fact, I've noticed that the only questions which go unanswered are the really difficult or esoteric ones (I've been known to ask a few of those ;-))
Best of luck to you, and come back whenever you need a hand.


Rob