help

[mark]

I have the following macro running in a workbook.
the problem i have with this is that when it creates the
new workbook with certain sheets missing, it changes the
orignal to the new workbook
can anyone help

Private Sub CommandButton1_Click()
Dim wb As Workbook
'copy all sheets
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
'delete the sheets you want
wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations
summary", "RCA incl RIM", "First Qtr", "Second
Qtr", "Third Qtr", "Fourth Qtr")).Delete
Application.DisplayAlerts = True

For Each Sh In wb.Worksheets
Sh.Columns("A:B").EntireColumn.Delete
Next
End Sub

 

[Nick]

Mark

No quite sure I understand the problem, are you trying to change a workbook
that is open from another workbook with the cosde in.

If you are then a better way thamn using ActiveWorkbook is to create a
workbook object variable
e.g.

dim wbNew as workbook.

set wbNew = workbooks.add

That way the variable is always assigned to the new workbook and not the
activeworkbook which if you are clicking a button must be the one containing
the code and not the one you want to work on.
Hope this helps.
Nick

 

[mark]

What I have is budget workbook, called Master Budget 05-
06 which I work on everyday. At the end of the month I
have to create a workbook minus certain sheets and
columns to send out to budget holder in order for them to
keep up to date on what they spend this is called Book 1.
This workbook is only for ref for that month and it can
be deleted. So when I run this macro it changes the
original (which is held in a database) to the book 1 and
the master then becomes the scrap one if you get my
drift.

thanks

Mark

 

[Nick]

I think I get you now.

Maybe a way to achieve this would be to use Thisworkbook.SaveAs and save the
master as a new book. Then the macro will run in the copied workbook.

Nick