Copying Source formatting and Values VBA

orangebloss

New Member
Joined
Jun 5, 2013
Messages
43
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!


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

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,981
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

New Member
Joined
Jun 5, 2013
Messages
43
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

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

New Member
Joined
Jun 5, 2013
Messages
43
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

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

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,778
Members
416,939
Latest member
Rajakumaran

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