Results 1 to 4 of 4

Thread: Excel VBA Copy & Paste Format & Values to new Workbook without Protection
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Location
    Louisville, KY
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Feb 2015
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  3. #3
    New Member
    Join Date
    Jun 2019
    Location
    Louisville, KY
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by ismii View Post
    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.

  4. #4
    Board Regular
    Join Date
    Feb 2015
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by ismii; Jun 10th, 2019 at 02:40 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •