Excel VBA Copy & Paste Format & Values to new Workbook without Protection

[aholland1970]

Hello,
I am new to the group, thanks for letting me join. We have a tool we use for estimating that I have been automating with VBA. When we win a project I need to take some of the sheets and copy them to a new workbook for a handoff document to project managers. The trick is that I would like to preserve the formatting (including column width) but paste only values removing all formulas. Many of the sheets are protected in the estimating tool and do not need to be protected in the copy. I have tried the Sheets (Array(.....)).Copy and using the .unprotect looping through the sheets. I don't want to have to put the password I use to protect the sheets in the VBA as I have read it's very easy to hack the VBA password. It seems maybe using an array of the sheets I need and the usedrange might work as I read if you copy a range of cells it does not bring over the sheet protection. It seems I would need to create a new workbook, take the same sheet names and then loop through the sheets copying only the usedrange (I have read this can have some bugs). I purchased the Ablebits tools which have alot of really nice tools and somehow they are doing it with their utility except their tool does not preserve the column width which is a pain.

Thanks if advance for any help. I am sure given enough time I could figure it out but I have a ton more to do and with all the experts here I can find out quicker.

Anthony

 

[ismii]

What about using an Inputbox to get the password? Then you won't be storing it, but can still loop through the sheets and copy what you need out.

 

[aholland1970]

What about using an Inputbox to get the password? Then you won't be storing it, but can still loop through the sheets and copy what you need out.

Thanks for the reply. There are people that will be using the tool that I do not want to have access to the password as the estimating tool is locked down for changes. Somehow the Alblebits add-in does it but they do not preserve the column widths. They much be manually creating the sheet names then copy and pasting based on usedrange.

 

[ismii]

Do you care if the columns are auto-fit, or do they need to be specific for each column?
something like below if you autofit

    Dim ourSheet As Worksheet
        Set ourSheet = ActiveSheet

.......

    ourSheet.Range("A:A,E:E,G:I").EntireColumn.AutoFit

UsedRange can cause some issues, so you can use something like a getLastRow / Column

Function getLastRow(Optional columnNumber As Long = 1, _
                    Optional wksht As Worksheet) As Long
    If wksht Is Nothing Then Set wksht = ActiveSheet
    getLastRow = wksht.Cells(wksht.Rows.Count, columnNumber).End(xlUp).Row
End Function

ALSO,
In VBA you can also copy over things for a protected sheet.
So in the "Immediate" pane, type Activesheet.Cells.Select , then go to the sheet and Ctrl-C. You'll see you can paste the sheet into a new window if you would like.