ukdane
07-14-2011, 06:38 AM
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.
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.