Copying Source formatting and Values VBA

[orangebloss]

I'm trying to copy cells with formulas in and paste special on another sheet - This works fine - but I need to copy over the formatting too (Colour of cells, size of font etc) but I seem to be going around in circles.

I tried recording the paste special values and formats which worked until I tried to put it into my VBA.

The code finds the last blank cell and pastes in the values, I then want it to paste the format of the copied cells in the same place - but no result

Any help appreciated!

Private Sub CommandButton1_Click()
  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  

  Set copySheet = Worksheets("Rough Cut")
  Set pasteSheet = Worksheets("Sandbox")
Set Destination = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

  
   Destination.PasteSpecial Paste:=xlPasteValues
 Destination.PasteSpecial Paste:=xlPasteFormats
  
  
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[mikerickson]

If you want the formatting along with the values, then .Paste rather than Pasting Special.
I was going to demonstrate the use of the Destination argument of the .Copy method, but you code doesn't have a .Copy line. That might be part of your issue.

 

[orangebloss]

Drat - it was there lol I've probably not copied it across - I'll update it tomorrow!

 

[orangebloss]

If you want the formatting along with the values, then .Paste rather than Pasting Special.
I was going to demonstrate the use of the Destination argument of the .Copy method, but you code doesn't have a .Copy line. That might be part of your issue.

The code is

Private Sub CommandButton1_Click()
  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
 

  Set copySheet = Worksheets("Rough Cut")
  Set pasteSheet = Worksheets("Sandbox")
  Set Destination = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  copySheet.Range("C27:DY39").Copy
 
   Destination.PasteSpecial Paste:=xlPasteValues
   Destination.PasteSpecial Paste:=xlPasteFormats
 
 
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

The source cells have formulas in which I don't want - only their values

 

[orangebloss]

If you want the formatting along with the values, then .Paste rather than Pasting Special.
I was going to demonstrate the use of the Destination argument of the .Copy method, but you code doesn't have a .Copy line. That might be part of your issue.

I'm going to admit to being an idiot - it helps if you update the right button click VBA.....
this now works beautifully

Private Sub CommandButton1_Click()
 Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
 Set copySheet = Worksheets("Rough Cut")
  Set pasteSheet = Worksheets("Sandbox")
  Set Destination = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  Set WksPlanned = Destination.Offset(0, 9)
  Set Totals = Destination.Offset(12, 18)
  copySheet.Range("C27:DY39").Copy
  
Destination.PasteSpecial Paste:=xlPasteFormats
   
  Destination.PasteSpecial Paste:=xlPasteValues
  copySheet.Range("L27:R39").Copy
  WksPlanned.PasteSpecial Paste:=xlPasteFormulas
  WksPlanned.PasteSpecial Paste:=xlPasteFormats
  copySheet.Range("U39:DY39").Copy
  Totals.PasteSpecial Paste:=xlPasteFormulas
  pasteSheet.Cells.RowHeight = 11.25
   pasteSheet.Rows("7:18").EntireRow.Hidden = True
   pasteSheet.Rows("1:2").EntireRow.Hidden = True
   pasteSheet.Activate

  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  
End Sub