automation

[Sherees]

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?

 

[Per Jessen]

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

 

[Bob I]

Select All Sheets and then change the Cell containing the dates

 

[Sherees]

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?

 

[Per Jessen]

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