Solved: Copy from one workbook, to another, keeping format.

[ukdane]

Hi Forum,
My brain's gone to sleep, I'm certain that this should be easy to solve, but alas....

Here's the situ.
User opens workbook (A). Does some stuff in it, presses a button, and my VBA script does some other stuff, including copying a worksheet from a different workbook (B) into the workbook they are currently using.

Here's the code I have for this.

Sub GetBulletin()
Dim XL As Excel.Application
Dim WB As Workbook
Dim MYWB As Workbook
Dim FILENAMEREPORT As String
Dim PATHREPORT As String
PATHREPORT = "mypath\"
FILENAMEREPORT = "myFile.xls"
Set XL = New Excel.Application
XL.Visible = False
Set Kalkulator = ThisWorkbook
Set WB = XL.Workbooks.Open(Filename:=PATHREPORT & FILENAMEREPORT)
WB.Worksheets("Sheet1").Copy
ThisWorkbook.Worksheets.Add After:=Sheets("ExistingSheet")
ActiveSheet.Name = "NewSheet"
MYWB.Worksheets("NewSheet").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 
WB.Close False
Set WB = Nothing
Set XL = Nothing
 
End Sub

Here's the crux. This code does not include copying any of the worksheet (B) layout (column sizes, fonts, etc etc), and I need it to. (Excel 2003)
Anyone help with a quick solution would be greatly appreciated.

Thanks in advance.

[AdrianK]

Try this:

 
With MYWB.Worksheets("NewSheet").Range("A1")
.PasteSpecial(xlPasteColumnWidths)
.PasteSpecial(xlPasteAll)
End with

This will paste all column widths then all numbers including formats/fills & row heights.

Alternatively if you are wanting everything that is on the other sheet (and creating a new sheet anyway) you could just copy the sheet to the other workbook?

WB.Worksheets("Sheet1").Copy After:=MYWB.Sheets("ExistingSheet")
MYWB.Sheets("Sheet1").name = "NewSheet"

Hope this helps.

Adrian

[ukdane]

Hi,
Thanks for your reply, but neither of these solutions work.
As stated in my original post I am using Excel 2003.
Or rather, I'm using Excel 2010, but my end users are using 2003, and so the file is being saved as a 2003 .xls file.

[AdrianK]

Sorry, i'm currently running 2003, so don't know any differences that 2010 might make (apart from being unable to copy worksheets as it automatically gives the sheet 1million+ rows), though I thought that if both files were saved as 2003 version that this would be fine.

Am being put onto 2010 within the next fortnight, once i'm on i'll have a play around see what I can come up with, but as I say at the moment I don't know any 2010 based issues.

Cheers,

Adrian

[ukdane]

Adrian,
Thanks for getting back to me. I have now solved this. The issue was that the other workbook (B) was being opened in a new Excel, not in the same Application.
I removed the "XL" parts from the code, and actually then went with the second suggestion that you had, as it was more precise, and correct for what I wanted to do.
Thanks for your help.

[afrpompeia]

Try this:

[vba]
With MYWB.Worksheets("NewSheet").Range("A1")
.PasteSpecial(xlPasteColumnWidths)
.PasteSpecial(xlPasteAll)
End with
[/vba]

This will paste all column widths then all numbers including formats/fills & row heights.

Alternatively if you are wanting everything that is on the other sheet (and creating a new sheet anyway) you could just copy the sheet to the other workbook?

[vba]
WB.Worksheets("Sheet1").Copy After:=MYWB.Sheets("ExistingSheet")
MYWB.Sheets("Sheet1").name = "NewSheet"
[/vba]

Hope this helps.

Adrian

See tittle. After a lot of tests I discover that the routine paste not from ExistingSheet but show clipboard data into NewSheet, (sorry my mistakes, english is not my native language)