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

[Ryanmm50]

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?

'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

 

[MARK858]

Maybe....

    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.

 

[Ryanmm50]

Figured it out! These changes using "with" seemed to work. I still had to activate wk1 before it would work. Any work around there?

'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]

Maybe....

    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]

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).

 

[Ryanmm50]

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]

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]

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]

You're welcome