Copy all named ranges but first unprotecting all sheets

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I have a macro that first unprotects all sheets from sheet3 and the copies the named range that match the sheet with the same name as the ranged sheet and then protects the sheet

I am battling to get this to work,

It would be appreciated if someone could assist me


Code:
 Sub Copy_Comm()
Application.DisplayAlerts = False

Dim i As Long
For i = 3 To Worksheets.Count


     
       Worksheets(i).Unprotect
      
    Next i
    

With Sheets(1)
    .Range("Peter).Copy
    
        Sheets("Peter).Range("A1").PasteSpecial Paste:=xlPasteValues
        End With
        
     Worksheets(i).Protect
   Next i
    

 End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Try putting double quotes around range and sheet names --> "Peter" not "Peter. Not sure what you are trying to do with the part after unprotecting sheet(3) and on, but there's a Next i (just before End Sub) that has no For mate..
 
Upvote 0
You should be getting an Error Message, "Expected Error" but you don't mention that in your post.
One big Problem is here:

Sheets("Peter) <--- Wrong: It is missing the closing quotations
Sheets("Peter") <-- Right

The loops are slightly off also.
Do the Sheets have Password Protection? (I'm guessing no, right?)
 
Last edited:
Upvote 0
Here is the code with loops fixed.
Still not really sure what you are trying to do.
But this will get you a lot closer:

Code:
 Dim i As Integer, LastRow As Long
    LastRow = Sheets("Peter").Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row
    
    'Loop Through Worksheets
    For i = 3 To Worksheets.Count
    
    'Unprotect Worksheet
    Worksheets(i).Unprotect
    
        'Work with The Unprotected Sheet:
        With Worksheets(i)
            Range("Peter").Copy
            'Copy To Above To Correct Sheet, But do so in the first empty cell
            Sheets("Peter").Range("A1:A" & LastRow).Offset(1).PasteSpecial Paste:=xlPasteValues
        End With
        
    'ReProtect WorkSheet
    Worksheets(i).Protect
    Next i

Hope this helps
 
Upvote 0
Use this as a launching pad instead:
It places the LastRow within the loop. I neglected to do that in my previous post.

In your post, you say each sheet has a name, with a matching named range.
To get the current Worksheet name you can use: Worksheet(i).Name to get the current worksheet's name.

Code:
    Dim i As Integer, LastRow As Long
    
    'Loop Through Worksheets
    For i = 3 To Worksheets.Count
    
    'Unprotect Worksheet
    Worksheets(i).Unprotect

        'Find the last row in Column A in Sheet Peter
        LastRow = Sheets("Peter").Cells(Sheets("Peter").Rows.Count, "A").End(xlUp).Row

        'Work with The Current Unprotected Sheet:
        With Worksheets(i)
            Range("Peter").Copy

            'Copy To Above To Correct Sheet, But do so in the first empty cell
            Sheets("Peter").Range("A1:A" & LastRow).Offset(1).PasteSpecial Paste:=xlPasteValues
        End With
        
    'ReProtect WorkSheet
    Worksheets(i).Protect
    Next i
 
Last edited:
Upvote 0
Now that the code is working, I would like to copy several range named ranged to their respective sheets for eg ranged name "Peter", "Steven", "Paula" to the sheets with the same names

I think that this needs to be set up as an array. It would be appreciated if you could amend your code to cater for this
 
Upvote 0
In that case you can probably use the sheet name to copy into the current sheet name....


UNTESTED:

Code:
        Dim i As Integer, LastRow As Long
        
        'Loop Through Worksheets
        For i = 3 To Worksheets.Count
        
        'Unprotect Worksheet
        Worksheets(i).Unprotect
    
            'Find the last row in Column A in Sheet Peter
            LastRow = Sheets("Peter").Cells(Sheets("Peter").Rows.Count, "A").End(xlUp).Row
    
            'Work with The Current Unprotected Sheet:
            With Worksheets(i)
                'Use The Worksheet Name to Return The Named Range
                Range(Worksheets(i).Name).Copy
                'Use The Worksheet Name to Copy The Above Named Range Into The Worksheet by the SAME NAME
                Worksheets(i).Range("A1:A" & LastRow).Offset(1).PasteSpecial Paste:=xlPasteValues
            End With
            
        'ReProtect WorkSheet
        Worksheets(i).Protect
        Next i

Hope this helps.
 
Last edited:
Upvote 0
Thanks for the help. This is great

I just need one small change. When pasting the data to the various sheets, I need the formatting to remain the same as on sheet1 for e.g. dates, percentages etc
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top