VBA CopyPaste Formatting

[RedOctoberKnight]

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.

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.

 

[Wookiee]

I'm not sure whether you're copying values from cells with formulas, so this code might seem redundant, but hopefully it helps.

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]

Thank you for your response.

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

 

[Wookiee]

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]

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]

How about

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]

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]

This works for me

      .Range("A4").PasteSpecial xlPasteValues
      .Range("A4").PasteSpecial xlPasteFormats
      .Range("A4").PasteSpecial xlPasteColumnWidths

 

[RedOctoberKnight]

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]

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.