Excel Spreadsheet Macro Question

I have a Macro that copies range I9:I22 from each sheet and pastes it to a summary sheet. The macro works great but when i go back to each sheet where the macro selected that range those cells are still black or selected. How do you write code into my macro to Unselect or unblacken those cells on each sheet?
Here is a copy of my macro.....

Sub Total_Hours()
Application.ScreenUpdating = False
Dim timesheet As Worksheet
    Dim Cell As Range
     For Each timesheet In ActiveWorkbook.Worksheets
        timesheet.Select
        Range("i9:i22").Select
        Selection.Copy
        Worksheets("total_hours").Activate
        ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlValues
        Application.CutCopyMode = False
    Next
    Range("b1").Select
   
End Sub
He4GivAsked:
Who is Participating?
 
nikelshConnect With a Mentor Commented:
i thought after pasting u have to go again on the main sheet and select one of the cell. ie.

Sub Total_Hours()
Application.ScreenUpdating = False
Dim timesheet As Worksheet
   Dim Cell As Range
    For Each timesheet In ActiveWorkbook.Worksheets
       timesheet.Select
       Range("i9:i22").Select
       Selection.Copy
       Worksheets("total_hours").Activate
       ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlValues
       Application.CutCopyMode = False
       timesheet.Select  ' select again
       Range("b1").Select ' any one cell in this sheet
   Next
   Range("b1").Select
     
End Sub

hope this will help u.
0
 
Brendt HessSenior DBACommented:
Try Range(I9:i9).Activate - this should cancel the selection, and set the active cell to the top of the range.
0
 
He4GivAuthor Commented:
That didnt work...Range("i9:i20").activate still shows that range on each worksheet as selected.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Richie_SimonettiIT OperationsCommented:
Try this:
Sub Total_Hours()
Application.ScreenUpdating = False
Dim timesheet As Worksheet
   Dim Cell As Range
    For Each timesheet In ActiveWorkbook.Worksheets
       with timesheet
            .Select
            .Range("i9:i22").Select
            Selection.Copy
            Worksheets("total_hours").Activate
            ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlValues
           Application.CutCopyMode = False
           .range("A1").select
       End With
   Next
   Range("b1").Select
   
End Sub
0
 
Richie_SimonettiIT OperationsCommented:
oops!
Sorry...
....
.....
....
False
          .range("A1").Activate
      End With
  Next
  Range("b1").Select
 
End Sub
0
 
He4GivAuthor Commented:
Works Great!!

Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.