mountainman88
Board Regular
- Joined
- Jun 22, 2019
- Messages
- 109
- Office Version
- 2016
- Platform
- Windows
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.
If I add the following the code errors:
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!
Code:
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:
VBA Code:
.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!