CODEWITHSUNDEEP
excelvbscript
Lucky
Lucky

Reputation: 383

cell borders not copied with paste special

Total newbie to VB Scripting. My question is 2 part: I need to copy a 450 column header from one excel sheet to another excel sheet with data. The header has table borders and is formatted. I looked up some examples online and put together this script which does the copy.

However, the cell borders are not being copied. Just the text - so the header formatting is not carried over.

below is my code.

is this not the correct way to paste the borders as well?

Once copied with borders, if I were to copy this sheet to another sheet, will the cell borders and formatting be carried over as well?

      Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = True

      Set objWorkbook1= objExcel.Workbooks.Open("header.xlsx")
      Set wksSource = objWorkbook1.Worksheets("Sheet1")
      Set objWorkbook2= objExcel.Workbooks.Open("IS3.xlsx")
      Set wksDest = objWorkbook2.Worksheets("TD")

       Set objRange = objWorkbook1.Worksheets("Sheet1").Range("A1:IZ3").Copy
       wksDest.Range("A1:IZ3").PasteSpecial  objRange
       objWorkbook1.Save
       objWorkbook1.Close

       objWorkbook2.Save
       objWorkbook2.Close

       objExcel.Quit
       Set objExcel = Nothing
       Set wksSource = Nothing
       Set wksDest = Nothing

Upvotes: 2

Views: 1747

Answers (1)

joeb
joeb

Reputation: 877

Your paste special has no directive. You'll need to hit it twice. Once for the formats, and once for the values. 

wksDest.Range("A1:IZ3").PasteSpecial  Paste:=xlPasteFormats objRange
wksDest.Range("A1:IZ3").PasteSpecial  Paste:=xlPasteValues objRange

Upvotes: 1

Related Questions