Macro to copy a range in a sheet...

[cadrose97]

I will start from top to bottom of what I need. FYI I am very very new to macros..

I need to copy a template sheet "BLANK FR" and paste it at the end of all the other sheets in the workbook for a new day. I have a macro for this step and it works just fine:

Sub CopyForemanReport()
Dim WS As Worksheet, WB As Workbook
Set WB = ActiveWorkbook
Set WS = WB.Sheets("BLANK FR")
WS.Copy After:=Sheets(WB.Sheets.Count)
End Sub

I have a summary sheet "Labour & Equipment Summary" with a range of cells in it that pulls data from the BLANK FR sheets that I create for each new day. So now, I need to copy the range of cells (A8:AS18) and paste that data below the summary range already on that sheet with a blank row in between. I only want to copy range (A8:AS18) every time - nothing more, nothing less. This is the macro I have set up for this step, except it copies the range I want plus everything I have added after it:

Sub CopySummary()

Dim LastRow As Long
Dim destRng As Range

Application.ScreenUpdating = False

With Sheets("Labour & Equipment Summary")
Set destRng = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 2)

LastRow = Sheets("Labour & Equipment Summary").Range("C" & Rows.Count).End(xlUp).Row
Sheets("Labour & Equipment Summary").Range("A8:AS18" & LastRow).Copy Destination:=destRng

End With
Application.ScreenUpdating = True
End Sub

Next, I need to update all of the formulas in the new summary range that I just pasted on the "Labour & Equipment Summary" sheet to reflect the new "BLANK FR (2)" sheet I just created, not any other range. I have a macro for this, but it updates the same range (B20:AR30) every time, not the new summary range I just pasted:

Sub UpdateSummary2NewDay()
'
' UpdateSummary2NewDay Macro
'
' Keyboard Shortcut: Ctrl+l
'
Range("B20:AR30").Select
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.Replace What:="BLANK FR", Replacement:="BLANK FR (2)", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

I hope someone understands what I need and can help me achieve it. As I said, I was just introduced to Macros yesterday and am quite impressed with how much I have accomplished already, just need to tweak it so it is perfect. AND if I could have all of these steps combined into one macro with a shortcut like (ctrl + j) that would be even better, versus 3 separate macros with 3 separate shortcuts. Thanks in advance!!

I have attached the spreadsheet as well.

Chantelle

[skywriter]

Let me know how this works out.

[cadrose97]

@skywriter..

OMG, you are amazing. It works perfectly, thank you so much!!

[skywriter]

Hold on a minute, I didn't see your request to combine them. I didn't combine them, it's better to leave them separate. But I've set this up so that control j will run them all.
Just make sure there's not an FR2 sheet if you run this more than once, if you want to check that it doesn't paste more than you wanted and all that other stuff, delete the copy sheet first.

[cadrose97]

Thanks for going the extra mile and combining all the macros into one macro for me, but I was able to complete that step myself.

Thanks again for all of your help!!