Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation
I created payslips in excel which totalled to more than 50 sheets, each
employees pay slip is in one sheet. Now every month I want to change payslip date and payment period dates in each sheet. what is the easiest way to do this work? can this work be recorded in macro and be done by a click of a button? -- Life isa journey not a destination |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation
Hi
Why not just enter new dates in Sheet1 and then in other sheets use a formula to get the dates from sheet1 like this: =Sheet1!A1 To insert formulas in all 49 sheets in one step, right click on a sheet tab, with any sheet but sheet1 active and 'Select all sheets' now hold down CTRL key and click on sheet 1 to unselect it, now you can write the formulas in one sheet and they are placed in all selected sheets. If you really want it, it can be done by a macro, but then we need a bit more info, like cells to change, shall it be changed in all sheets or is there sheet(s) to be excluded. Regards, Per "Sherees" skrev i meddelelsen ... I created payslips in excel which totalled to more than 50 sheets, each employees pay slip is in one sheet. Now every month I want to change payslip date and payment period dates in each sheet. what is the easiest way to do this work? can this work be recorded in macro and be done by a click of a button? -- Life isa journey not a destination |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation
Select All Sheets and then change the Cell containing the dates
Sherees wrote: I created payslips in excel which totalled to more than 50 sheets, each employees pay slip is in one sheet. Now every month I want to change payslip date and payment period dates in each sheet. what is the easiest way to do this work? can this work be recorded in macro and be done by a click of a button? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation
I dont know macro at all, so pls educate me as a beginner--
say for eg. i nedd to change the cell D6,D7&E7 in all the sheets what is to be done in macro? what is to be done if i want to exclude some sheets from the change? Please write both seperately and elaborately because i am totally new to use macro. meanwhile let me try what u have suggested. And let me ask which would be the easiest way (time saving) the one u suggested in this letter or setting the macro? -- Life isa journey not a destination "Per Jessen" wrote: Hi Why not just enter new dates in Sheet1 and then in other sheets use a formula to get the dates from sheet1 like this: =Sheet1!A1 To insert formulas in all 49 sheets in one step, right click on a sheet tab, with any sheet but sheet1 active and 'Select all sheets' now hold down CTRL key and click on sheet 1 to unselect it, now you can write the formulas in one sheet and they are placed in all selected sheets. If you really want it, it can be done by a macro, but then we need a bit more info, like cells to change, shall it be changed in all sheets or is there sheet(s) to be excluded. Regards, Per "Sherees" skrev i meddelelsen ... I created payslips in excel which totalled to more than 50 sheets, each employees pay slip is in one sheet. Now every month I want to change payslip date and payment period dates in each sheet. what is the easiest way to do this work? can this work be recorded in macro and be done by a click of a button? -- Life isa journey not a destination . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automation
Lets take the last question first, I would prefer the formula solution, as
the date change in all sheets once a date is entered, just rember that the date can only be changed in 'Sheet1' else your formula will be overwritten, and no date valus change in other sheets. Once the macro is working as desired, you do not have to worry about overwriting formulas. To insert the macro, open the VBA editor (ALT+F11) Insert Module Paste the macro into the code sheet and run it. The macro will ask for the three dates to be changed, and loop through all sheets excluding the sheet named 'NotThisSheet' and change the dates. Sub ReplaceDates() Dim pSlip As Date Dim StartPeriod As Date Dim EndPeriod As Date pSlip = InputBox("Enter Payslip date", "Change Dates") StartPeriod = InputBox("Enter Payment Period Start", "Change Dates") EndPeriod = InputBox("Enter Payment Period End", "Change Dates") For Each sh In ThisWorkbook.Sheets If sh.Name < "NotThisSheet" and sh.Name <"ExcludeThisSheet" Then 'Remove if no sheets are to be excluded With sh .Range("D6") = pSlip .Range("D7") = StartPeriod .Range("E7") = EndPeriod End With End If 'Remove if no sheets are to be excluded Next End Sub Regards, Per "Sherees" skrev i meddelelsen ... I dont know macro at all, so pls educate me as a beginner-- say for eg. i nedd to change the cell D6,D7&E7 in all the sheets what is to be done in macro? what is to be done if i want to exclude some sheets from the change? Please write both seperately and elaborately because i am totally new to use macro. meanwhile let me try what u have suggested. And let me ask which would be the easiest way (time saving) the one u suggested in this letter or setting the macro? -- Life isa journey not a destination "Per Jessen" wrote: Hi Why not just enter new dates in Sheet1 and then in other sheets use a formula to get the dates from sheet1 like this: =Sheet1!A1 To insert formulas in all 49 sheets in one step, right click on a sheet tab, with any sheet but sheet1 active and 'Select all sheets' now hold down CTRL key and click on sheet 1 to unselect it, now you can write the formulas in one sheet and they are placed in all selected sheets. If you really want it, it can be done by a macro, but then we need a bit more info, like cells to change, shall it be changed in all sheets or is there sheet(s) to be excluded. Regards, Per "Sherees" skrev i meddelelsen ... I created payslips in excel which totalled to more than 50 sheets, each employees pay slip is in one sheet. Now every month I want to change payslip date and payment period dates in each sheet. what is the easiest way to do this work? can this work be recorded in macro and be done by a click of a button? -- Life isa journey not a destination . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automation | Excel Worksheet Functions | |||
Automation Error | Excel Discussion (Misc queries) | |||
Automation?? | Excel Discussion (Misc queries) | |||
automation | New Users to Excel | |||
Workbook automation | Excel Discussion (Misc queries) |