Monthly Spreadsheet for Equipment Hours- I'm stuck!

[Closed]
Report
Posts
2
Registration date
Thursday February 13, 2014
Status
Member
Last seen
March 5, 2014
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
Daily log for equipment hours

I want to make a file with 31 days of worksheets in the workbook for the complete month.

The order of the worksheets in the file are as follows:

The first worksheet will be a start summary of the hours for the previous month.

The next 31 worksheets are 1 through 31 for each day of the month

The last worksheet will basically contain the information for the end of the day for the last day of the month in order to copy and paste special values only for the start of the next months start summary. It will be unaffected even if the number of days in the month vary.

One column example:

Sheet 1 --- End of Previous Month Summary Sheet

Cell Location: (Sheet 1~~~~Cell A1)
Cell Contents: User entered number for end of the month hours

Finished look:

100 hours


Sheet 2--- Day 1

Cell Location: (Sheet 2 ~~~~Cell A1)
Cell Contents: User entered number for end of the day hours
Use: Today's Total Hours - (value entered at the end of the day by user)

Cell Location: (Sheet 2 ~~~~Cell A2)
Cell Contents: =+'sheet 1'!A1
Use: Hours from end of previous day - (value pulled from Sheet 1 ~~~~Cell A1

Cell Location: (Sheet 2 ~~~~Cell A3)
Cell Contents: =A1-A2
Use: Today's Runtime - ( simple calculation of the values from cell Sheet 2 ~A1 subtracted from cell Sheet 2~A2 "the above cells")

Finished look:

124 Today's Hours
100 Previous Hours
24 Hours Runtime

The next day will be set up as follows:
Sheet 3--- Day 2

Cell Location: (Sheet 3 , Cell A1)
Cell Contents: User entered number for end of the day hours
Use: Today's Total Hours - (value entered at the end of the day by user)

Cell Location: (Sheet 3 , Cell A2)
Cell Contents: =+'sheet 2'!A1
Use: Hours from end of previous day- (value pulled from Sheet 2 - Cell A1

Cell Location: (Sheet 3 - Cell A3)
Cell Contents: =A1-A2
Use: Today's Runtime - (simple calculation of the values from cell Sheet 3 - A1 minus Sheet 3~A2 "the above cells on the same sheet")

Finished look:

148 Today's Hours
124 Previous Hours
24 Hours Runtime

That was the easy part......

Now to easily make 31 days worth with multiple columns of numbers.

"Sheet 4",

I right click on the tab, select move or copy, create a copy. Rename the new tab as "sheet 4"

By doing the above method of making a new worksheet, the formulas come out the same as the previous sheet and will pull information from the same (sheet 2) place.

Cell A1 is user entered number and the number transfers over as the same as the copied sheet.

Cell A2 is now copied as: =+'sheet 2'!A1

Cell A3 is copied as the normal "=A1-A2" calculation that is calculated from cells within the same sheet. No problems.

I would like for the formula in cell A2 to automatically update itself to:

=+'sheet 3'!A1

when making the next sheet.

How do I get the new sheet to pull the corrected cell formulas without going in and correcting them manually.

With close to 40+ cells of information to be pulled from one sheet and transferred to the next day manually going in and changing this 1240+ times to set up one month would be too much.

Any Help is greatly appreciated.
Thanks.

A

2 replies

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi A,

Created a new workbook with one sheet and named it Sheet 1.
Each time the below code is run, a new sheet is added, renamed and formula is added to A2 to refer to the previous' sheet A1:
Sub RunMe()
Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Sheet " & Sheets.Count + 1
Range("A2").Formula = "='Sheet " & Sheets.Count - 1 & "'!A1"
End Sub

Now you will have to add the other formulas to the code to just simple run the code to create a new sheet.

Best regards,
Trowa

Posts
2
Registration date
Thursday February 13, 2014
Status
Member
Last seen
March 5, 2014

Thanks for the reply TrowaD.
I'm not familiar with the proper terminology that is used for excel.
Running this code...
Where is this code entered?
When you say add the formula to the code, Is it all entered into the cell?

I apologize for my short dictionary on terminology used.

Best Regards,
A
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
No worries A,

Let's look at your questions:

Where is this code entered?
After opening Excel, hit Alt+F11 to open the Visual Basic window.
Go to top menu > Insert > Module.
Copy-Paste the code in the big white field.
You can now close the Visual Basic window.
Back at Excel, hit Alt+F8 to open a small window showing the available Macro's.
Double-click on RunMe to "run the code".

When you say add the formula to the code, Is it all entered into the cell?
You said in your original post that you had 40+ cells that needs to pulled.
Since I don't know the source nor the destination, I couldn't add them to the code.
That is what I meant by adding the formulas to the code.

The formula in the code now looks like:
Range("A2").Formula = "='Sheet " & Sheets.Count - 1 & "'!A1"

Here Range("A2").Formula refers to the destination cell on the newly created sheet.
The actual formula in code form is "='Sheet " & Sheets.Count - 1 & "'!A1"
In excel it will look like ='Sheet #'!A1. Where # refers to the sheets number (second to last, since the newly created sheet will be the last sheet).

So if you would like to pull data from A10 from the previous sheet and place it in A5. The code line would look like:
Range("A5").Formula = "='Sheet " & Sheets.Count - 1 & "'!A10"

To put the formula =A1-A2 in cell A3, use the code line:
Range("A3").Formula = "=A1-A2"

Place these code lines just above the End Sub line.


Hopefully I have clarified some issues.
Let me know if further assistance is desired.

Best regards,
Trowa