vba copy & paste to variable range

nitrammada

Board Regular
Joined
Oct 10, 2018
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,
I've had tremendous help from so many on this forum to get me this far, I have adopted peoples suggestions and the code I have below works, but it only works on the active sheet.
I'm trying to get this to work on the very hidden sheets. Would any one be kind enough to tell me where I'm going wrong please? I'm trying to copy a formula to cell W7 and then copy the formula from cell W7 to cell W8 downwards. Data in column D dictates the number of rows in each sheet. The last row will vary on each of the 140 odd very hidden sheets.

VBA Code:
Sub CopyToW8()
    'Copy & paste formula to all sheets on each row in col W
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect

Dim lWS_Visible_State As Long
Dim ws As Worksheet

    For Each ws In Worksheets
        '        lWS_Visible_State = ws.Visible
        '        ws.Visible = xlSheetVisible
        ws.Unprotect
        
        If ws.Range("A1") = "L2" And ws.Range("M6") = "H5" Then
        ws.Range("W7") = "=PRODUCT(Q7:V7)"
        End If
        '        ws.Visible = lWS_Visible_State
        ws.Protect
    Next ws
        

Dim Count, i As Long
Count = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
'MsgBox Count
i = 8
    If Range("A1").Value = "L2" Then
        Do While i <= Count
            Range("W7").Select
            Selection.Copy
            Range(Cells(i, 23), Cells(i, 23)).PasteSpecial Paste:=xlPasteFormulas
            Application.CutCopyMode = False
        i = i + 1
        Loop
    End If
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Thanks in advance for any suggestions.
adam
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
for the worksheets that are xlSheetVeryHidden you must first change the Visible property to either xlSheetVisible or xlSheetHidden to manipulate it and then you can reset it back to xlSheetVeryHidden
 
Upvote 0
for the worksheets that are xlSheetVeryHidden you must first change the Visible property to either xlSheetVisible or xlSheetHidden to manipulate it and then you can reset it back to xlSheetVeryHidden
Thank you Crystalyzer, I shall give that a go, thank you very much, appreciate the direction.
Regards
adam
 
Upvote 0
VBA Code:
Sub Test()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim wsVH As Boolean
    
    Set wb = ThisWorkbook
    wsVH = False
    
    For Each ws In wb.Worksheets
        With ws
            If .Visible = xlSheetVeryHidden Then
                wsVH = True
                .Visible = xlSheetHidden  'change to xlSheetVisible if you wish
            End If
            
            [YOUR CODE HERE]
            
            If wsVH Then
                wsVH = False
                .Visible = xlSheetVeryHidden
            End If
        End With
    Next ws
End Sub
 
Upvote 0
Solution
VBA Code:
Sub Test()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim wsVH As Boolean
   
    Set wb = ThisWorkbook
    wsVH = False
   
    For Each ws In wb.Worksheets
        With ws
            If .Visible = xlSheetVeryHidden Then
                wsVH = True
                .Visible = xlSheetHidden  'change to xlSheetVisible if you wish
            End If
           
            [YOUR CODE HERE]
           
            If wsVH Then
                wsVH = False
                .Visible = xlSheetVeryHidden
            End If
        End With
    Next ws
End Sub
Hi Crystalyzer, thank you very much for giving me the code. I tried it as per my code below but i must have something not quite right, it runs perfectly if the sheet is visible but wont run if the sheet is very hidden. Have i put my code in the correct place?

VBA Code:
Sub CopyToW8_4()
'From Crystalyzer
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim wsVH As Boolean
    
    Set wb = ThisWorkbook
    wsVH = False
    
    For Each ws In wb.Worksheets
        With ws
            If .Visible = xlSheetVeryHidden Then
                wsVH = True
                .Visible = xlSheetHidden  'change to xlSheetVisible if you wish
            End If
            
            Dim Count, i As Long
            Count = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
            MsgBox Count
            'i = 8
            If Range("A1").Value = "L2" Then
                Do While i <= Count
                    Range("W7").Select
                    Selection.Copy
                    Range(Cells(i, 23), Cells(i, 23)).PasteSpecial Paste:=xlPasteFormulas
                    Application.CutCopyMode = False
                i = i + 1
                Loop
            End If
            
            If wsVH Then
                wsVH = False
                .Visible = xlSheetVeryHidden
            End If
        End With
    Next ws
End Sub
 
Upvote 0
Try changing this line of code
.Visible = xlSheetHidden 'change to xlSheetVisible if you wish
to
.Visible = xlSheetVisible 'change to xlSheetVisible if you wish
 
Upvote 0
Try changing this line of code
.Visible = xlSheetHidden 'change to xlSheetVisible if you wish
to
.Visible = xlSheetVisible 'change to xlSheetVisible if you wish
Same thing, sorry to say. Works if it visible but still not working if very hidden.
 
Upvote 0
Try changing this line of code
.Visible = xlSheetHidden 'change to xlSheetVisible if you wish
to
.Visible = xlSheetVisible 'change to xlSheetVisible if you wish
I notice one instance in the code it has Dim ws As Worksheet in another instance it is wb. Worksheets does the 's' on the end of Worksheets make a difference?
 
Upvote 0
I notice one instance in the code it has Dim ws As Worksheet in another instance it is wb. Worksheets does the 's' on the end of Worksheets make a difference?
YES!! they are two different objects.

ws is set to a single worksheet object while wb.Worksheets is ALL the worksheets in the workbook.
 
Upvote 0
YES!! they are two different objects.

ws is set to a single worksheet object while wb.Worksheets is ALL the worksheets in the workbook.
Hello Crystalyzer, It works, thank you so much, I played around with it a little after what you had said and it works a charm. Thanks so much for your time and effort.
Regards
Adam
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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