VBA script Please

[chadlmiller]

OK MS EXCEL 2003

I know it can be done but my script skills are not savy enough.

I want to use an Excel work book and develop a macro or VBA script that does the following without a great deal of link correction:

I want to click on a button to create a new sheet that is an exact duplicate of a template worksheet (So it has formulas and format consistencies) and be able to enter a NAME for the new Worksheet, the NAME appear in cell:A2 of new NAME worksheet and the NAME appears on the Main sheet in the next available Blank row of cell A? with the NAME and then the row also copies the previous row Formula with the new NAME in the formula:
2 Examples: currently
first: when the new NAME is placed in the next empty cell I would like the NAME to LINK to the new sheet with the same NAME.
Second if the previous row looks like this cell F18=SUM(Kirstin!D282)
Then I want the next row to populate with information like this upon new sheet creation.
cell F19=SUM(NAME!D282)


I know this is a lot, but any part or part of this example would be of great benefit. Thank you, Chad

 

[SkipVought]

Have you tried using your macro recorder? Do so, and then post any code where you are having problems.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[chadlmiller]

Yes, I started with a Macro which I recorded but the next times I ran the macro the same NAME Sheet and NAME cells were attempted to be created and that created a conflict. and I destroyed the original file I had created.

I guess I simply do not know how to either record a generic macro enough or I have a too complicated question.

 

[SkipVought]

"but the next times I ran the macro the same NAME Sheet and NAME cells were attempted to be created and that created a conflict"

Well that's exactly the kind of help you'll get; resolving such conflict.

Your code please.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[chadlmiller]

Sub CreateSheetMacro()
'
' CreateSheetMacro Macro
' Macro recorded 2/15/2008 by Chad
'

' Created a new sheet

Sheets("Template").Select
Sheets("Template").Copy Before:=Sheets(7)
Sheets("Overall Girl Totals").Select
Selection.Copy
Sheets("Template (2)").Select
ActiveSheet.Paste ' Copy & Pasted the FIRSTNAME from the Master sheet into the A2 cell
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit ' I autosized the coloum to fit the longer name
Selection.ShapeRange.Item(1).Hyperlink.Follow NewWindow:=False, AddHistory _
:=True 'I clicked on a box which is hyperlinked to the master sheet
ActiveWindow.SmallScroll Down:=6
Range("F24:Q24").Select ' I selected and copied the last row of formulas which are designed to read the number from the new sheet
Application.CutCopyMode = False
Selection.Copy
Range("F27").Select 'I went to the specific cell I want to begin my paste which is 3 column left of the NEW FIRSTNAME
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("F27:Q27").Select 'Now since the formula has the wrong FIRSTNAME I did Find and replace in the selected cells of the proper row
Selection.Replace What:="Skylar", Replacement:="FIRSTNAME", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Sheets("Template (2)").Select
Sheets("Template (2)").Name = "FIRSTNAME" 'I wasn't prepared I forgot the type the NEW SHEET with the correct FIRSTNAME so I had to rename the new sheet
Selection.ShapeRange.Item(1).Hyperlink.Follow NewWindow:=False, AddHistory _
:=True
ActiveWindow.SmallScroll Down:=9
Range("C25").Select 'Then I tried to take the new FIRSTNAME cell and create a hyperlink to the new FIRSTNAME Sheet
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"FIRSTNAME!A1", TextToDisplay:="FIRSTNAME"

End Sub

 

[SkipVought]

One thing that you ought to do when you macro record, is not rely on current selections.

What is the master Sheet? "Copy & Pasted the FIRSTNAME from the Master sheet into the A2 cell" WHERE in the Master Sheet are you getting FIRSTNAME.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[SkipVought]

OK. Forget the PROCESS that you have conceived to accomplish this task.

What is the purpose of these new sheets?

How are they related to the data on the master sheet?

What is the data on the master sheet?


Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[chadlmiller]

ok here was the thought process:

Open master file: Which is a summary of all girls listed which is selling girl scout cookies.

click on a box which has a macro or VBA code to do the following:

Open a dialog box to enter NEWNAME
then a template is copied and a new sheet is created where the SHEETNAME:NEWNAME
Then the NEWNAME SHEET cell A2=NEWNAME
Then the master sheet finds the next blank cell in column C and adds a HYPERLINK with text of NEWNAME and link to the NEWNAME sheet.
Then F column new row cell get the following formula =SUM(NEWNAME!D282) and the G column gets the =SUM(NEWNAME!E2:E82) formula and so on until it completes to the P Column.

I hope that is good psuedo code

Chad

 

[SkipVought]

You're stuck on YOUR process. I don't care about YOUR process.

What I want to know is WHAT you are trying to do. Why do you need the new sheet? What does it do for you? How does this new sheet function?

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[chadlmiller]

ok,
I want to be able to issue a three sheet workbook to other units.

this means the MASTER SHEET has no names. Units with limited excel knowledge should be able to enter a name and the workbook does the rest. Until the data is entered in the INDIVIDUAL sheets then the formulas take over to give qnty of boxes or cases to order, record the numbers of pick up and monies owed by individuals.


What each sheet is intended to do:
an INDIVIDUAL sheet captures the cookie inventory issued and monies collected (ie. how much an individual has to order by unit qnty)
the MASTER sheet provides a summary of each of the individual sheets (ie. how much is owed based on the total taken and type)
An SUMMARY sheet tells summary of how many boxes of cookies to collect from the distribution point. (ie. how many

 

[SkipVought]

This is what I understand.

You have a table that should contain
[tt]
ProductID
ProductPrice
[/tt]
[tt]
This is the main table that contains the core of your business...
SalesPerson
ProductID
OrderDate
OrderQty
PmtAmount
PmtDate
[/tt]
From tables like this, you can generate SUMMARY reports or individual reports.

Seems like you have the cart before the horse. As such, you have devised a very difficult PROCESS that, in reality, thwarts many of Excel's data analysis and data reporting features.



Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[chadlmiller]

I would have to say yes that is the core in a simple format.
any suggestions?

Would you prefer to see the workbook as is and make suggestions?

 

[SkipVought]

That CORE table is where ALL the data need to be accumulated. Then you would not need all this other VBA code.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[chadlmiller]

I understand what you are saying, bu the dynamic here is working with young girls and house moms, who do not understand excel formulas and reports and such and all they want want to see is a sheet that displays each girls total individually

It cannot look like a running collected total or a jumble of totals because they would not understand how to segregate the girls out by date or such.

I have tried what you are speaking of last year and it was a total failure. when I showed them the new format they fell in love with it, but the creating a sheet and formula population is time consuming.

 

[SkipVought]

I did not say ANYTHING about formulas!

The KEY is that if ALL your source data were in ONE table, then you can REPORT each scout's statistices separately in a matter of SECONDS using the PivotTable wizard, one of Excel's powerful reporting features.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[chadlmiller]

Yes, that I do understand, I use that features quite easily and I find it very powerful.

But the problem is that others who would not have direct support (i.e. Girls and Women out of state and people who want this excel workbook to track scout cookie activities are unable to use such advanced features. This is why I started this thread to find an easier method of creating a scout sheet. Maybe it simply cannot be done. Sorry to take so much of your time, I thank you for your help.

 

[SkipVought]

Could you not incorporate a "front end" (could be a simple as a Data > Validation - LIST) for the moms to select the scout that you want to see the data for? Use the selection to drive the Pivottable PAGE FIELD.

The key point is that proceeding for a SOURCE TABLE to any summary report a far and away simpler than trying to combine summaries from disparate locations (sheets).

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[chadlmiller]

redesign of the work book at this point is not the option I was seeking.

I tried yrou suggestion last year and the users were too confused.

I found a system that they liked, worked well in and the formulas worked to provide SOLID results.

My current problem is the updating with new names and creating new sheets. If this is impossible to do via a MAcro or VB script then I will have to perform this tedious task myself. I understand. if there is a method of doing my task via macro, then I would be grateful. Thank you for all your input. I will plan ahead in the future to make this run smoother for next year.

 

[SkipVought]

Sure, it's possible. Sure, I can use a butter knife to drive in a screw, but it's not a best and accepted practice.

You have an extremely complicated process, dictated by something other than logic. You will be plagued by problems in both the development and maintenance.

Here's an example of the first several statements reconditioned...

    Dim wsNEW As Worksheet

    Sheets("Template").Copy Before:=Sheets(Sheets.Count)
    Set wsNEW = ActiveSheet
    Sheets("Overall Girl Totals").Cells.Copy
    wsNEW.Paste   ' Copy & Pasted the FIRSTNAME from the Master sheet into the A2 cell
    wsNEW.Columns("A:A").EntireColumn.AutoFit ' I autosized the coloum to fit the longer name

Ther remainder will be tedious

What is this reference for this selection? You say its a BOX. How many boxes are there? So when the click the box, the master sheet is activated?

    Selection.ShapeRange.Item(1).Hyperlink.Follow NewWindow:=False, AddHistory _
        :=True    'I clicked on a box which is hyperlinked to the master sheet
[code]


Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
[b]He's now living in OBJECT poverty![/b][tongue][/sub]

 

[SkipVought]

Also, I have a feeling that you edited your code and left some stuff out, like regarding the cell reference for FIRSTNAME.

So where does FIRSTNAME come from, since, "...this means the MASTER SHEET has no names"

I am clearly confused!!!!

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]