VBA CopyPaste Formatting

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
98
Office Version
  1. 2016
Platform
  1. Windows
Good Morning,

I have the following code which copies and pastes from one workbook to another. It only pastes the values though and I'm unsure on how to write it so it pastes formatting too.


VBA Code:
Public Sub Copy_Range_From_Truck_Schedules()

       Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim tsWorkbook As Workbook

Set tsWorkbook = Workbooks.Open(ThisWorkbook.Path & "\truckschedulesdualsides.xlsx")
With ThisWorkbook.ActiveSheet
.Range("A4:R200").Value = tsWorkbook.Worksheets(.Name).Range("A4:R200").Value

End With
tsWorkbook.Close False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Any help would be much appreciated.

Thanks.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Wookiee

Active Member
Joined
Nov 27, 2012
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
I'm not sure whether you're copying values from cells with formulas, so this code might seem redundant, but hopefully it helps.

VBA Code:
Public Sub Copy_Range_From_Truck_Schedules()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim tsWorkbook As Workbook
Dim rngCopy As Range

Set tsWorkbook = Workbooks.Open _
   (ThisWorkbook.Path & "\truckschedulesdualsides.xlsx")
   
Set rngCopy = tsWorkbook.Worksheets(.Name).Range("A4:R200")
rngCopy.Copy

With ThisWorkbook.ActiveSheet

  .Range("A4").Select
  Selection.PasteSpecial xlPasteValues
  Selection.PasteSpecial xlPasteFormats
  
End With

tsWorkbook.Close False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
 

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
98
Office Version
  1. 2016
Platform
  1. Windows
Thank you for your response.

However i'm getting a "invalid or unqualified reference" error. It's highlighting the .Name
 

Wookiee

Active Member
Joined
Nov 27, 2012
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
Thank you for your response.

However i'm getting a "invalid or unqualified reference" error. It's highlighting the .Name

Sorry about that. I copied your code and didn't realize that the way I restructured that would cause an error. I think it should work if you change .Name to ActiveSheet.Name.
 

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
98
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Sorry to be a pain but now I get a range error.

.range("A4").select is giving me an error for some reason.

Thanks,
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,991
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Public Sub Copy_Range_From_Truck_Schedules()
   
   Application.Calculation = xlCalculationManual
   Application.ScreenUpdating = False
   Dim tsWorkbook As Workbook
   
   Set tsWorkbook = Workbooks.Open(ThisWorkbook.Path & "\truckschedulesdualsides.xlsx")
   With ThisWorkbook.ActiveSheet
      tsWorkbook.Worksheets(.Name).Range("A4:R200").Copy
      .Range("A4").PasteSpecial xlPasteValues
      .Range("A4").PasteSpecial xlPasteFormats
   End With
   tsWorkbook.Close False
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub
 

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
98
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks Fluff, Works perfectly.

Curious how I go about copying the column widths too?

I tried adding .Range("A4").pastespecial xlpastecolumnwidths but I get a range error.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,991
Office Version
  1. 365
Platform
  1. Windows
This works for me
VBA Code:
      .Range("A4").PasteSpecial xlPasteValues
      .Range("A4").PasteSpecial xlPasteFormats
      .Range("A4").PasteSpecial xlPasteColumnWidths
 

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
98
Office Version
  1. 2016
Platform
  1. Windows
Thanks Fluff.

Does the order matter? I had it above the pastevalues originally.

Also, I get an alert of lots of data on the clipboard. Is there a way clear the clipboard once the data is pasted?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,991
Office Version
  1. 365
Platform
  1. Windows
It works for me with it anywhere. But I think I've seen posts where it does seem to matter what order they are in.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,649
Messages
5,597,368
Members
414,139
Latest member
okela0

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