VBA for Copied Version of Workbook 1

[shelby55]

Hi

I'm using Excel 2003 and I'm a newbie at it.

I'm using the following code to copy a workbook to another folder:
Private Sub Workbook_Open()
Sheets(Array("MyWSA", "MyWSB", "MyWSC", "MyWSD")).Copy
Application.DisplayAlerts = False
Application.CutCopyMode = False
ActiveWorkbook.SaveCopyAs Filename:="E:\My Documents\MyFolder\WBCopy_" & Format(Now(), "YYYYMMDD") & ".xls"
ActiveWindow.Close

This is working except I need to know how to write code to the new work book to:
- protect cells from being editable
- hide raw data worksheet (MyWSD)
- deselect the pages

Also, on the first worksheet there is a data validation cell that is for users to select what date from the raw data sheet they wish to view data on. This isn't working in the copy and I don't know why.

Any help greatly appreciated.

 

[Gruuuu]

- protect cells from being editable

For each ws in workbook.Sheets
    ws.Protect "password"
Next ws
workbook.protect "password"

- hide raw data worksheet (MyWSD)

Worksheets("MyWSD").Hidden = True
'Or
Worksheets("MyWSD").VeryHidden = True
'If you're worried about people getting being clever and unhiding the sheet

- deselect the pages

workbook.Sheets(1).Select

Putting it all together:

...
    Dim ws as Worksheet
    Dim NewWB as Workbook

    Sheets(Array("MyWSA", "MyWSB", "MyWSC", "MyWSD")).Copy
    [red][b]Set NewWB = ActiveWorkbook[/b][/red] <--Try to always set your ambiguous objects to object variables.
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    For each ws in [red][b]NewWB[/b][/red].Sheets
        ws.Activate
        ws.Protect "password"
    Next
    [red][b]NewWB[/b][/red].Protect "password2"
    [red][b]NewWB[/b][/red].Sheets("MyWSD").Hidden = True
    [red][b]NewWB[/b][/red].Sheets(1).Select
    [red][b]NewWB[/b][/red].SaveCopyAs Filename:="E:\My Documents\MyFolder\WBCopy_" & Format(Now(), "YYYYMMDD") & ".xls"
    ActiveWindow.Close

 

[shelby55]

Hi

Thanks very much. I used the code but I got a runtime error 438 "object doesn't support this property or method" which was caused by the line NewWB.Sheets("MyWSD").Hidden = True.

Why would that be - thanks.

 

[shelby55]

Hi

Sorry but another thing: as I mentioned, I have a cell that is a data validation using a named range. It is now not working because it is protected.

Is it not possible to just protect some cells but not all. In this case, all other cells can be protected except this one.

Having said that, when it wasn't protected I couldn't get it to work. It allowed me to select another date but it wouldn't change the summary data on the worksheet when it did.

I really appreciate any help you can give - thanks.

 

[PHV]

NewWB.Sheets("MyWSD").Visible = xlSheetVeryHidden

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi PHV

Thanks very much but it doesn't like that either - now it's a runtime error 1004 - "unable to set the visible property of the Worksheet class".

Other ideas? Thanks.

 

[PHV]

NewWB.Sheets("MyWSB").Select
NewWB.Sheets("MyWSD").Visible = xlSheetVeryHidden

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi PHV

Thanks but it's still objecting to the NewWB.Sheets("Raw Data").Visible = xlSheetVeryHidden statement.

Same error error 1004 - "unable to set the visible property of the Worksheet class".

 

[SkipVought]

Unprotect the sheet

Select any cell(s) you want to be able to change

Right click > Format Cells > Protection TAB: Uncheck LOCKED

Protect the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks but I need to do this programatically - is that possible? Or will I have to do what you suggested on the original so it copies to the copy?

Also, any ideas about why the data validation named range isn't working on the copy?

Thanks.

 

[SkipVought]

Turn on your macro recorder and have at it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Thanks Skip. One more thing: I'm still not clear on how to refer to the copy worksheet/workbook instead of the one I'm writing the code in.

For instance, if I want to delete the query connection how will I do that for the copy? And I still haven't figured out why the data validation named range won't work on the copy - without it there is no reason to copy this!

Thanks.

 

[SkipVought]

For instance, if I want to delete the query connection how will I do that for the copy?

dim wbCopy as workbook

Set wbCopy = [i]your copy workbook[/i]
'assuming you have only one QT on the sheet...
wbCopy.Sheets("YourQuerySheet").QueryTables(1).delete

the workbook containing your code is ThisWorkbook

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip.

I have everything working as I want it to EXCEPT the biggie which is the data validation named field.

All the named fields copied over to the copy. The formulae for the summary page is the same as the original. In the copy the only summary that is showing is the most recent date in the list of dates.

Can you think of what the problem is on this? Thanks.

Shelley

 

[SkipVought]

check in the copied wb to see if you have links to the original wb

Edit > Links

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Before you replied I looked into many of the named ranges and you are right - some went from ='User Days'!$B$5:$C$6 in the original to ='E:\MyDocuments\MyFolder\Test2_July 7 2010\[ED-PIP DART Prototype V3.2_B.xls]User Days'!$B$9:$C$10

So how do I stop that from happening?

Thanks.

Shelley