VBA Faster avoiding ".select" and ".activate"

Ryanmm50

New Member
Joined
Jun 26, 2017
Messages
13
Can someone help me with ".find" without using select? I'm switching between multiple workbooks to get a summary of the data. How can I select the DT workbook, unprotect the sheet, and find the correct worker (from variable) and copy it? Then select the next workbook (where the summarized data goes) and paste the data?

VBA Code:
'Copies the DT hours and pastes in the Hours workbook
    wkDT.Activate
    Sheets("Labor").Unprotect
    Sheets("Labor").Select
    Range("B3").Activate
    Cells.Find(What:=wrkr, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, MatchCase:=False).Activate
    ActiveCell.Offset(32, 0).Select
    Selection.Copy
    
'pastes the values into the new sheet
    wK1.Activate
    Sheets("Hours").Select
    Cells(lastRow, 4).PasteSpecial xlPasteValues
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,194
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe....
VBA Code:
    With wkDT.Sheets("Labor")
        .Unprotect
        wK1.Sheets("Hours").Cells(lastRow, 4).Value = _
          .Cells.Find(What:=wrkr, After:=.Range("B3"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows).Offset(32, 0).Value
    End With

Edit: You will probably find that the .UnProtect line is unnecessary with the code above but I haven't tested it.
 
Last edited:
Solution

Ryanmm50

New Member
Joined
Jun 26, 2017
Messages
13
Figured it out! These changes using "with" seemed to work. I still had to activate wk1 before it would work. Any work around there?

VBA Code:
'Copies the DT hours and pastes in the Hours workbook
    With wkCR.Sheets("Labor")
        .Unprotect
        .Cells.Find(What:=wrkr, After:=Range("B3"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, MatchCase:=False).Offset(32, 0).Copy
        End With
        
    wK1.Activate
    
    With wK1.Sheets("Hours")
        Cells(lastRow, 5).PasteSpecial xlPasteValues
    End With
 

Ryanmm50

New Member
Joined
Jun 26, 2017
Messages
13
Maybe....
VBA Code:
    With wkDT.Sheets("Labor")
        .Unprotect
        wK1.Sheets("Hours").Cells(lastRow, 4).Value = _
          .Cells.Find(What:=wrkr, After:=.Range("B3"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows).Offset(32, 0).Value
    End With

Edit: You will probably find that the .UnProtect line is unnecessary with the code above but I haven't tested it.
Thanks for the reply I didn't even check as I've been trying to figure it out myself. The problem I was having is "Hours" is in a different workbook than "labor". I think I've got it now. Thanks for the help! (Also, the sheets are usually either protected (hopefully) or unprotected so better to have it in there JIC. Thanks again!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,194
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

You don't need to activate anything, your 2nd With statement is doing nothing as you haven't qualified Cells (which is why you had to Activate the workbook).
 
Last edited:

Ryanmm50

New Member
Joined
Jun 26, 2017
Messages
13
You don't need to activate anything, your 2nd With statement is doing nothing as you haven't qualified Cells (which is why you had to Activate the workbook).
How can I qualify the cells? Would that just be wk1.cells(....)? I'm self taught so not too familiar with all the terms. Thanks for the help!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,194
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

You need a full stop/period in front of the word Cells, then you can remove the Activate line.

The .Value = .Value syntax I posted in the other code is faster than the Pastespecial syntax as it doesn't use the clipboard.
 

Ryanmm50

New Member
Joined
Jun 26, 2017
Messages
13
How can I qualify the cells? Would that just be wk1.cells(....)? I'm self taught so not too familiar with all the terms. Thanks for the help!
Got it! Thanks so much. That's why I'd never make it as a programmer. Lack of attention to detail. I fixed it up the way you said and it is a bit faster (especially when doing like a 24 month query)! Thanks so much for your help!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,194
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Forum statistics

Threads
1,141,309
Messages
5,705,674
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top