VBA Copy contents of leftheader into cell(s)

[bosco2]

I am trying to copy the contents (actual words, no formatting information) into cells on a worksheet.

I currently assign the leftheader to a variable:
leftheader = OpenBook.Sheets("sheet1").PageSetup.leftheader

But when I assign it to a cell
ThisWorkbook.Worksheets("Sheet2").Range("A1") = leftheader

It puts all the information into one cell including the format/font information (i.e. &"Trebuchet MS,Regular"&G)

Is there a way to paste it into a cell the same way if I just copied/pasted manually the header content. (it has an address so I would expect it to paste into multiple cells). The caveat is that I am copying the leftheader information from worksheet 1 and pasting it into cell in worksheet 2.

Thank you.

Also asked here VBA Copy content of LeftHeader into cells

 

[JLGWhiz]

I suspedt you will have to use some worksheet funtions to like LEFT() and MID() to pare the LeftHeader down to only the text. Something like

txt = Sheet2.PageSetUp.LeftHeader
Sheet2.Range("A1") = Mid((txt, 2, InStrRev(txt, "&") -3)