VBA paste special not working as expected

[mountainman88]

Im looking to copy data from one sheet Sh1 to another Sh3 which varies based on a list being looped through, I DO NOT want to duplicate the sheet, I want to retain all formatting and remove gridlines and freezepanes. The below code has an issue where all the hidden rows from Sh1 are unhidden when pasted, and all the row heights are uniform, not the same row heights as Sh1.

For Each c In sh2.Range("a1", sh2.Cells(Rows.Count, 1).End(xlUp))
        Set sh3 = Sheets(c.Value)
        sh1.UsedRange.Copy
        
        With sh3
        .Activate
        .Range("a1").PasteSpecial xlPasteAll
        .Range("a1").PasteSpecial xlPasteFormats
        .Range("a1").PasteSpecial xlPasteColumnWidths
        .Range("A1").Activate
        .Range("C5").Activate
       End With
       ActiveWindow.FreezePanes = True
       ActiveWindow.DisplayGridlines = False
    Next c

If I add the following the code errors:

.Range("a1").PasteSpecial xlPasteRowHeights

How do I make sure the copied sheet is identical to the original with all data, formulas, row height, hidden rows retained (or subsequently replicated)?

Any help would be appreciated thanks!

 

[DanteAmor]

.Range("a1").PasteSpecial xlPasteRowHeights

That type does not exist:

XlPasteType enumeration (Excel)

Office VBA reference topic

docs.microsoft.com

But it can be done as follows:

Sub CopySheet()
  Dim c As Range
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim i As Long
 
  Application.ScreenUpdating = False
 
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  For Each c In sh2.Range("a1", sh2.Cells(Rows.Count, 1).End(xlUp))
    Set sh3 = Sheets(c.Value)
    sh1.UsedRange.Copy
   
    With sh3
      .Activate
      .Range("a1").PasteSpecial xlPasteAll
      .Range("a1").PasteSpecial xlPasteFormats
      .Range("a1").PasteSpecial xlPasteColumnWidths
      'xlPasteRowHeights
      For i = 1 To sh1.UsedRange.Count
        .Rows(i).RowHeight = sh1.Rows(i).RowHeight
      Next
      .Range("A1").Activate
      .Range("C5").Activate
      ActiveWindow.FreezePanes = True
      ActiveWindow.DisplayGridlines = False
    End With
  Next c
End Sub

 

[Alex Blakenburg]

If Dante's code works for you then run with that. If it doesn't,
does your UsedRange in Sheet 1 start at A1 ?
If not do you want to shift the output to A1 in the destination ?
Do you want formulas and constanta to be in the destination ?

To achieve what you want I would start with copying the whole sheet. This will ensure you get the hidden rows/column and the heights and widths some across.
Sheet1.Cells.Copy sh3.Range("A1")
What you do afterwards depends on the responses to the above questions.

 

[mountainman88]

Thanks for this ill try it.
When I use select all ctrl-c and then select all ctrl-c I get what I need
When I use the format painter to copy from one sheet to another I get exactly what I need, why can this not be replicated when using paste format in VBA?

thanks