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

aholland1970

New Member
Joined
Jun 10, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

ismii

Board Regular
Joined
Feb 5, 2015
Messages
150
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

New Member
Joined
Jun 10, 2019
Messages
2
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

Board Regular
Joined
Feb 5, 2015
Messages
150
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
Code:
    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
Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,353
Messages
5,601,136
Members
414,430
Latest member
jtdinh205

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top