VBA paste special not working as expected

mountainman88

Board Regular
Joined
Jun 22, 2019
Messages
99
Office Version
  1. 2016
Platform
  1. 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.

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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,393
Office Version
  1. 2013
Platform
  1. Windows
.Range("a1").PasteSpecial xlPasteRowHeights
That type does not exist:


But it can be done as follows:

VBA Code:
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
 
Solution

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,629
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 22, 2019
Messages
99
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,181,680
Messages
5,931,375
Members
436,788
Latest member
er19

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
Top