macro to paste value unhidden columns

[howard]

I have the following code below to copy and paste data on certain sheets as follows


I would like the code amended so that only the data on the undidden columns are copied and pasted as values

Your assistance is most appreciated

Sub ValueCopyCommSheets()


Dim i As Long
For i = Sheets("BTCON").Index To Sheets("BRDA East").Index
Unhide_All_Columns_in_Workbook
With Sheets(i)
' If .Visible Then
.UsedRange.Copy

.UsedRange.PasteSpecial Paste:=xlPasteValues


End With
Next i

End Sub

 

[mohadin]

Hi
Try

Sub ValueCopyCommSheets()
Dim i As Long
For i = Sheets("BTCON").Index To Sheets("BRDA East").Index
With Sheets(i)
If .Visible Then
With .UsedRange
.Value = .Value
End With
Else
.Visible = True
End If
End With
Next i
End Sub

 

[HaHoBe]

Hi howard,

not so certain about the requirement (this code will exclude hidden sheets) so maybe

Sub ValueCopyCommSheetsVisibleRange()
' https://www.mrexcel.com/board/threads/macro-to-paste-value-unhidden-columns.1222670/
Dim i           As Long
Dim rngArea     As Range

For i = Worksheets("BTCON").Index To Worksheets("BRDA East").Index
'  Unhide_All_Columns_in_Workbook
  With Worksheets(i)
    If .Visible Then
      For Each rngArea In .UsedRange.SpecialCells(xlCellTypeVisible).Columns
        rngArea.Value = rngArea.Value
      Next rngArea
    End If
  End With
Next i

End Sub

Ciao,
Holger

 

[howard]

Many Thanks forgthe help. Code works perfectly