Macro to copy table only values and formatting, not formulas|VBA & Macros|Excel Forum|My Online Training Hub

Hello everybody,

I hope you can help me.

I need to create a macro to copy a table from one sheet and paste it to another sheet, including the values and the format, the width of columns (but not the formulas).

The sheet is protected and the table has dynamic rows. That's why I've preferred not to define the range as number of rows.

The macro listed here below works but it copies and pastes the table, also included the formulas...

thnks for your support

Mark

*************************************************************

'Name macro
Sub CopyFilteredTable()

'Dimension variables and declare datatypes
Dim rng As Range
Dim WS As Worksheet

Set WS = ThisWorkbook.Sheets("Table1")
ThisWorkbook.Sheets("Table1").Unprotect Password:="XXXXXX"

'Go through rows in Table1
For Each Row In Range("Table1").Rows

'Check if row is visible
If Row.EntireRow.Hidden = False Then

'The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object.
If rng Is Nothing Then Set rng = Row

'Returns the union of two or more ranges.
Set rng = Union(Row, rng)
End If

'Continue with next row
Next Row

'Create a new worksheet
Set WS = Sheets.Add

'Copy rng to cell A1 in worksheet WS
rng.Copy Destination:=WS.Range("A1")
ThisWorkbook.Sheets("Table1").Protect Password:="XXXXXXXX"
End Sub

**************************************************************************************