Unselect copied sheets before close

[jazminecat23]

Hi all -

I have a procedure that copies some workbooks into a new spreadsheet and saves that spreadsheet for me. When I then open the saved sheet, all of the sheets are selected. I'd like to unselect the sheets before the save executes. Should I just select sheet1 cell a1? Or is there another way to have nothing selected?

Thanks!

 

[PHV]

Select a single sheet:
...
yourWorkbookObject.Sheets(1).Select
yourWorkbookObject.Save
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat23]

Hi PHV -

thanks for your reply. That doesn't seem to work. When I open the newly created workbooks, all the sheets are still selected.

Here's my code for copying the data values into them:

    For Each WS In wbNew.Worksheets
      WS.Cells.Copy
      WS.Cells.PasteSpecial xlPasteValues
      WS.Sheets("Budget Status").Select
    Next

This is the piece that runs right before the save, and it's to convert the formulas into values. I figured that would be the spot to put this code. I also tried this:

 With wbNew
     .Sheets("Budget Status").Select
     .SaveAs "I:\SpreadsheetServer\Month End Reports" & "\" & sName & ".xls"
     .Close
    End With

Which also doesn't solve the problem. Currently, when I open any of my newly created workbooks (there ends up being over 200 of them, sent to a variety of endusers) all of the worksheets in the workbook are selected/highlighted. It's confusing for the endusers, so I need to find a way to make them open all white, or not-selected.

Thanks!

 

[PHV]

Sorry, misunderstood the issue.
What about this ?
For Each WS In wbNew.Worksheets
WS.Cells.Copy
WS.Cells.PasteSpecial xlPasteValues
WS.Cells(1, 1).Select
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat23]

OO, that's making progress. Now the first sheet is white. But all the others (there are 4 more) are still all blue (highlighted/selected). Wierd! I'm running 2000, I wonder if it would behave differently in 2003?

 

[PHV]

What is YOUR actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat23]

Here's my code:

'copy master sheets
 Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
    "Detailed Report", "JE Detail")).Copy
    On Error Resume Next

    
    
    Set wbNew = ActiveWorkbook
    With wbNew
        .Sheets("JE Detail").Columns("D:F").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("H:L").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("N:Q").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("V:Z").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("AA:AF").EntireColumn.Hidden = True
        'hide zero lines
        Application.Run ("HideZeroLines")
    End With
    Sheets("BudgetStatus").Select
   
    
    
'7. Replace formulas with values in new workbook

    For Each WS In wbNew.Worksheets
      WS.Cells.Copy
      WS.Cells.PasteSpecial xlPasteValues
      WS.Cells(1, 1).Select
      WS.Cells(1, 2).Select
    Next
    
'8. save and close new workbook
        
    With wbNew
     .SaveAs "I:\SpreadsheetServer\Month End Reports" & "\" & sName & ".xls"
     .Close
    End With

 

[SkipVought]

'copy master sheets
 Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
    "Detailed Report", "JE Detail")).Copy
    On Error Resume Next

    
    
    Set wbNew = ActiveWorkbook
    With wbNew
        .Sheets("JE Detail").Columns("D:F").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("H:L").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("N:Q").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("V:Z").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("AA:AF").EntireColumn.Hidden = True
        'hide zero lines
        Application.Run ("HideZeroLines")[b]
        .Sheets("BudgetStatus").Select[/b]
    End With[b]
'isn't this sheet in the wbNew workbook???  I move to above
'    Sheets("BudgetStatus").Select[/b]
   
    
    
'7. Replace formulas with values in new workbook

    For Each WS In wbNew.Worksheets
      WS.Cells.Copy
      WS.Cells.PasteSpecial xlPasteValues[b]
'      WS.Cells(1, 1).Select  why this statement???[/b]
      WS.Cells(1, 2).Select
    Next
    
'8. save and close new workbook
        
    With wbNew
     .SaveAs "I:\SpreadsheetServer\Month End Reports" & "\" & sName & ".xls"
     .Close
    End With

Skip,

_[red][/red]

 

[jazminecat23]

Hi again Skip! Hey, I don't get the other error you were helping me with when I run this on Excel 2003, just so you know, so that seems to have taken care of itself since my endusers run 2003. Wierd huh?

I see what you mean in the first bit - I will try that. The second part

WS.Cells(1, 1).Select why this statement???

I was trying to get it to select one cell, then another, in another attempt to get it to unhighlight/select the entire workbook. This because while it's now working on the first sheet in the workbook, the others are entirely blue/selected now *except* for cell A1, which is white. Thanks!!

 

[SkipVought]

You can't SELECT a range in a sheet that is not active...

    For Each ws In wbNew.Worksheets
        ws.Activate
        ws.Cells.Copy
        ws.Cells.PasteSpecial xlPasteValues
        ws.Cells(1, 1).Select
    Next

Skip,

_[red][/red]