Help with VBA

[efarmer9538]

I am trying to move a worksheet from one workbook and save it to another. I keep getting an automation error and every change I make to that line I keep getting error messages of different kinds. I am so close to having this speadsheet the way I need it to be but this is stopping me.

Private Sub CommandButton1_Click()

Dim OrgWs As Worksheet
Dim NewWs As Worksheet



Set OrgWs = ActiveSheet

OrgWs.copy After:=Worksheets(Worksheets.Count)

Set NewWs = ActiveSheet

NewWs.Name = Format(Date, ("ddmmmyy"))

OrgWs.Move After:=Workbooks("Normand Flower POB 2014 09.xlsm").Worksheets(Worksheets.Count)


OrgWs.Name = Format(Date - 1, ("ddmmmyy"))

Windows("NF POB SEP 2014.xlsm").Activate

End Sub

Is there anyway I can make this sheet go to the workbook without having it open?

Thanks!

 

[Momentman]

You seem to have the answer to the question or so i think

You need to open that workbook first of all, copy the sheet to it and then save and close.

 

[efarmer9538]

Thanks but I need to do this automatically with VBA

 

[GTO]

...

Is there anyway I can make this sheet go to the workbook without having it open?

Thanks!

Hi there,

No, but we could see if "Normand Flower POB 2014 09.xlsm" is open first, and open it if not. Would that help?

Which workbook has the code in it, might it be "NF POB SEP 2014.xlsm"?

Mark

 

[Momentman]

Thanks but I need to do this automatically with VBA

I never suggested doing it manually, I was only suggesting what extra lines/steps to incorporate into the VBA code e.g

Workbooks.Open ("Normand Flower POB 2014 09.xlsm")

to open the workbook before copying a sheet to it.....

 

[efarmer9538]

I never suggested doing it manually, I was only suggesting what extra lines/steps to incorporate into the VBA code e.g

Workbooks.Open ("Normand Flower POB 2014 09.xlsm")

to open the workbook before copying a sheet to it.....

Yeah sorry about that. I thought you meant do it all manually which of course would defeat the purpose

It almost works. I am so close

 

[Momentman]

Does this work for you?

Private Sub CommandButton1_Click()


    Dim OrgWs As Worksheet
    Dim NewWs As Worksheet
    
    Set OrgWs = ActiveSheet
    
    OrgWs.Copy After:=Worksheets(Worksheets.Count)
    Set NewWs = ActiveSheet
    
    NewWs.Name = Format(Date, ("ddmmmyy"))
    Workbooks.Open ("Normand Flower POB 2014 09.xlsm")
    
    OrgWs.Move After:=Workbooks("Normand Flower POB 2014 09.xlsm").Worksheets(Worksheets.Count)
    OrgWs.Name = Format(Date - 1, ("ddmmmyy"))
    ActiveWorkbook.Save
    
    Windows("NF POB SEP 2014.xlsm").Activate
End Sub