worksheet name to appear in cell

[Mr BT]

Hello, thank you to all that answer the following post

I have set up my workbook to simulate my company's order forms. For each
day's work I could add as few as 5 to as many as 20. This ng has assisted
many times with developing my workbook to this point.

One of the things it currently does, will be to insert a new worksheet tab,
copy and paste sort of a template of the order form, then rename it "temp"
which would be renamed again based on the customer info i collect. I would
then enter the customer information and repeat those steps for the next
order/customer.

As I've improved the workbook's functionality (based on my requirements) I
have it calculating the total amount owed, then in three different fields I
would enter the amount paid in cash, cheque or charge.

Now what I want to do with each of these fields is to have a separate
worksheet totalling the cash, cheque and charge amounts. And the following
formula has been given...

=SUM('OF1:Insert Template DO NOT DELETE'!BY225)
OF1........ worksheet is a blank worksheet
Insert Template DO NOT DELETE........ is the Order Form template

Instead of one field on my Cash Counter worksheet using the above formula, I
would like to have it a bit different and I haven't been able to figure out
how to do this...

I would like Cash Counter to have the following
A B
Cash cash Amount
OrderFormWorksheet1 =OrderFormWorksheet1!B10
OrderFormWorksheet2 =OrderFormWorksheet2!B10




=Count(customers using cash) =TotalChashAmount


OrderFormWorksheet1 =OrderFormWorksheet1!B11
OrderFormWorksheet2 =OrderFormWorksheet2!B11



=Count(customers using cheques) =TotalChequesAmount


Charge Charge Amount
OrderFormWorksheet1 =OrderFormWorksheet1!B12
OrderFormWorksheet2 =OrderFormWorksheet2!B12



=Count(customers using credit cards) =TotalChargeAmount


But there is a problem with how I would like this to work and how I
understand how to make it work...

I need to have a way to insert a row under each of the above sections (cash,
cheque, and charge) when a new worksheet is inserted. I also need it to
refer to the new worksheet name when its changed from "temp" to the
customer's account name.


Can someone help me?
If you need me to explain any part of this further to help you help me,
please ask.

Thank you,


Mr BT

 

[Mark Ivey]

For referencing the sheet name...

While the sheet is still active, I would suggest to apply it (the sheet's
name) to a temporary variable:

Dim shName As String
shName = ActiveSheet.Name ' now you can use this "string" name of the
sheet in another location in your code

As for your other requests, I am a bit unclear on all you are wanting. If
you want you an email me an example and I will look it over. Please provide
as much detail as possible if you opt to email it to me.

Mark Ivey
(e-mail address removed)