Excel Macro Page Setup Not Retained

[TTEL]

I have a macro which opens a workbook, runs a query, copies the data to a new workbook and then emails this new workbook before closing the original workbook.

However when I attempt to perform page setup in the macro on the newly created workbook (to fit to 1 page and make landscape) the settings are lost when the sheet is emailed and it reverts to 100% and portrait.

Does anyone know a way around this.


Thanks,


CAB

 

[KenWright]

Is it by any chance a possibility that in your code the Original workbook stays active, you haven't explicitly referenced the new workbook in the setup code, and that in fact your original workbook is the one that gets the page setup settings. If the original is not then saved the settings would then disappear and appear never to have taken anywhere.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[TTEL]

Gave this a try but to no avail.

Any further suggestions much appreciated.


Regards

 

[xlbo]

post your code then...

btw - this should really be asked in the VBA forum...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[TTEL]

Sub Workbook_Open()

Application.DisplayAlerts = False

Sheets("UK").Select
Range("A4").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

' nothing to email/no orders added
If ActiveCell.Value = "" Then GoTo FINISH


Dim strFileName As String
Dim strFilePath As String


strFileName = "UK_ORDERS_ADDED_EMAIL.xls"
strFilePath = "\\uk\"

Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False



ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


Dim objEMail As Object

Set objEMail = CreateObject("CDONTS.NewMail")
' From must be a valid e-mail address!
objEMail.From = "it@t.co.uk"
objEMail.To = "sales@t.co.uk"
objEMail.Body = "Please find attached the GORE_UK_ORDERS_ADDED for " & Format(Date, "DD/MM/YY")
objEMail.Subject = "UK_ORDERS_ADDED - " & Format(Date, "DD/MM/YY")
objEMail.MailFormat = 0 ' mime format
objEMail.attachfile strFilePath & strFileName
objEMail.Send
Set objEMail = Nothing

FINISH:
ActiveWorkbook.Save

Application.Quit
End Sub

 

[xlbo]

I think Ken has this one nailed on the head:
try this:

Sub Workbook_Open()
dim newWB as workbook

    Application.DisplayAlerts = False

    Sheets("UK").Select
    Range("A4").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    
    ' nothing to email/no orders added
    If ActiveCell.Value = "" Then GoTo FINISH
    
    
    Dim strFileName As String
    Dim strFilePath As String
    
  
    strFileName = "UK_ORDERS_ADDED_EMAIL.xls"
    strFilePath = "\\uk\"
    
    Cells.Select
    Selection.Copy
    [b]set newWB[/b] = Workbooks.Add
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=False
          
    [b]newWB[/b].SaveAs Filename:=strFilePath & strFileName, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

You do not have your pagesetup code in here though....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[TTEL]

Thanks very much for your help chaps.
I've got this working now.