Select/Activate a Cell From Another Workbook

[rootdown42]

I have a template that, using VBA, makes a copy of itself, opens the copy, does a copy/paste-values over the data in each worksheet, and saves that copy. After its done and a user opens the copied file, all the cells on the worksheet are still selected from the Copy method, which I want to avoid. I attempted to add a Activate statement and later a Select statement into the code, but that just results in the always enjoyable "Run-time error 1004 - Select/Activate Method of Range class failed" error. The relevant code looks like this:

With wb
     For each ws in .Worksheets
          With ws
              .Cells.Copy
              .Cells.PasteSpecial Paste:=3
              Application.CutCopyMode = False
              .Cells(7, 6).Select   <---- Runtime Error
          End With
     Next ws
End With

I assume that its incorrect to use the Select method inside the With blocks - in fact I thought it was best practice to not use .Select or .Activate at all in my code - but I don't know any other method to unselect ever single cell within my copied file. Can anyone help?

 

[NateO]

Hello,

What is 3? Values?

My thought would be to not select anything, e.g.,

     For each ws in wb.Worksheets
          With ws.UsedRange
              .Value = .Value
          End With
     Next ws

*untested - aircode*

Otherwise, check out the GoTo Method.

 

[VoG]

Try this alternative approach

With wb
     For Each ws In .Worksheets
          With ws.UsedRange
            .Value = .Value
          End With
     Next ws
End With

 

[rootdown42]

Thank you both for the reply. The UsedRange method property didn't work - maybe because I used Cells.Copy before the PasteSpecial? Regardless, the Application.Goto method seemed to have solved the problem.

And to answer your question NateO, yes, the 3 in

.Cells.PasteSpecial Paste:=3

is supposed to represent xlPasteValues. I used the numeric reference because it was a suggested work-around for an even worse problem I'm having with Automation Error that I'm encountering. Thanks again.