Unselecting for multiple worksheets in VBA

[ukanez]

Hello all,

My current macro copies certain sheets over and pastes as values. I'm having problems trying to get each individual work sheet to reset their selection, or at least select cell (1,1).

Application.ScreenUpdating = False
Sheets(Array("BC", "AB", "SK", "MB", "Prairies", "ON", "QC", "NB", "PE", "NS", "NF", "Atlantic")).Copy
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Cells.Copy
    ws.Cells.PasteSpecial (xlValues)
    Cells(1, 1).Select
Next

The first sheet in the new file selects cell(1,1) but when I look at the other sheets the selection remains the entire sheet.

Thanks for your help!

 

[Fluff]

Hi & welcome to the board.
You haven't qualified the cells in the last line of loop so it will always be looking at the active sheet. Try

ws.Cells(1, 1).Select

 

[ukanez]

Hi & welcome to the board.
You haven't qualified the cells in the last line of loop so it will always be looking at the active sheet. Try

ws.Cells(1, 1).Select

Thank you. I'm getting a 1004 error that says the Select method of Range class failed. I tried switching that to ws.Range(A1).Select and got the same error. Am I missing something?

 

[Fluff]

Try

For Each ws In ActiveWorkbook.Worksheets
    ws.activate
    ws.Cells.Copy
    ws.Cells.PasteSpecial (xlValues)
    ws.Cells(1, 1).Select
Next

 

[ukanez]

Thank you very much! Is there any way to add ScrollRow into this code or make each sheet scroll to the top of the page? Having this work properly, I've come to realize that while it selects (1,1), it doesn't actually scroll to the top which is part of why I wanted to select it in the first place.

 

[Fluff]

How about

For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
    Application.Goto ws.Cells(1, 1)
Next ws