Help with VBA

efarmer9538

New Member
Joined
Sep 13, 2014
Messages
3
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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
...

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
 
Upvote 0
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
Code:
Workbooks.Open ("Normand Flower POB 2014 09.xlsm")
to open the workbook before copying a sheet to it.....
 
Upvote 0
I never suggested doing it manually, I was only suggesting what extra lines/steps to incorporate into the VBA code e.g
Code:
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
 
Upvote 0
Does this work for you?

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top