Advice on code improvement and completing last items in Workbook

[JaceRich]

Hi, I'm new to Excel and any related forums and am hoping to get some direction on completing this current project. I have done pretty well with a ton of google searches to get me where I'm currently at, but now seem to be stuck on getting the last bit of functionality I'd like.

I currently have a template I made that populates as a new worksheet in this workbook, and positions itself behind my first sheet. I have another macro that I pasted below. It currently does what I want correctly (up to this point), but I feel this is not the correct way to do this (copying and pasting each cell) and causes the screen to flash while its copying/pasting.

Since it might alter the advise given, the last bit I'm trying to get figured out to complete the functionality is as follows:

1. I'm trying to have the 1st worksheet be a dashboard, listing all the other worksheets values as a summary page.
2. After the template is completed, I'd like the Save and Print macro button I have to transfer the data to my Summary page
3. The below macro currently changes the name of the sheet to the last name (B3) just like I want, but I'm having a hard time figuring out how to hide the sheet, and the row with checkbox on the Summary page once a check mark I use for "picked up" is checked on the Summary page since the sheet name is unknown beforehand. (I have an activeX checkbox that can do this when I list the exact sheet name in quotes manually, but that checkbox fills with white background after selected so I'd like another way)

Any help on any part of this would be greatly appreciated. I just recently fell in love with Excel but am pretty green to not only the program, but VBA coding as well.




Sub PrintandSave()
'
' PrintandSave Macro
'

'
On Error Resume Next
ActiveWorkbook.Save
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("TEMPLATE").Select
Range("B3").Select
Selection.Copy
Sheets("Total Losses").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("TEMPLATE").Select
Range("C3").Select
Selection.Copy
Sheets("Total Losses").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("TEMPLATE").Select
Range("E3").Select
Selection.Copy
Sheets("Total Losses").Select
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("TEMPLATE").Select
Range("B5").Select
Selection.Copy
Sheets("Total Losses").Select
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("TEMPLATE").Select
Range("C5").Select
Selection.Copy
Sheets("Total Losses").Select
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("TEMPLATE").Select
Range("E12").Select
Selection.Copy
Sheets("Total Losses").Select
Range("H3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("TEMPLATE").Select
Range("E13").Select
Selection.Copy
Sheets("Total Losses").Select
Range("I3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("TEMPLATE").Select
Range("E14").Select
Selection.Copy
Sheets("Total Losses").Select
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("TEMPLATE").Select
Range("E16").Select
Selection.Copy
Sheets("Total Losses").Select
Range("K3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Dim rs As Worksheet

For Each rs In Sheets
If rs.Index > 1 Then
rs.Name = rs.Range("B3")
End If
Next rs
End Sub

 

[JLGWhiz]

this should get rid of the flicker and flash. But I don't understand the rest of what you want to do.

Sub PrintandSave()
'
Dim rs As Worksheet 'Declarations should be at top so memory is reserved for them
 ActiveWorkbook.Save
 ActiveWindow.SelectedSheets.PrintOut Copies:=1
    With Sheets("TEMPLATE")
        .Range("B3:C3").Copy
        Sheets("Total Losses").Range("B3").PasteSpecial xlPasteValues
        .Range("E3").Copy
        Sheets("Total Losses").Range("D3").PasteSpecial xlPasteValues
        .Range("B5").Copy
        Sheets("Total Losses").Range("E3").PasteSpecial xlPasteValues
        .Range("C5").Copy
        Sheets("Total Losses").Range("F3").PasteSpecial xlPasteValues
        .Range("E12").Copy
        Sheets("Total Losses").Range("H3").PasteSpecial xlPasteValues
        .Range("E13").Copy
        Sheets("Total Losses").Range("I3").PasteSpecial xlPasteValues
        .Range("E14").Copy
        Sheets("Total Losses").Range("J3").PasteSpecial xlPasteValues
        .Range("E16").Copy
        Sheets("Total Losses").Range("K3").PasteSpecial xlPasteValues
    End With
    For Each rs In ThisWorkbook.Sheets 'Good habit to always qualify your collection to avoid default to Active object.
        If rs.Index > 1 Then
            rs.Name = rs.Range("B3").Value
        End If
    Next rs
End Sub