Prev: Cell colour format according to date
Next: Autocomplete Custom Functions and functions arguments
From: Louise on 20 May 2010 07:05
Hi All,

I need some help please...

I have a workbook (which due to many pivots and formulae is massive), I need
a macro to export groups of sheets to new workbooks (each time it is run)
then return to the workbook and export another group for example:

I need Belgium & Netherlands to be exported (values only) to one workbook,
then from the original workbook I need Spain & Switzerland exported to
another fresh workbook, so in the end I have 3 files, one original, one with
Bel & Neth, and the other with Spa & Swi. I also need the data to be exported
as values only.

Currently I have the code below but it errors on workbook("Test").Select

Sheets(Array("Netherland", "Belgium")).Select
ActiveWindow.SelectedSheets.Copy

Workbook("Test").Select

Any help much appreciated
From: Jacob Skaria on 20 May 2010 07:42
I didnt get what workbook "Test" is? If you want to select or save the new
workbook try the below....

Dim wb As Workbook
Sheets(Array("Netherland", "Belgium")).Copy
Set wb = ActiveWorkbook
wb.SaveAs "c:\test.xls"


--
Jacob (MVP - Excel)


"Louise" wrote:

> Hi All,
>
> I need some help please...
>
> I have a workbook (which due to many pivots and formulae is massive), I need
> a macro to export groups of sheets to new workbooks (each time it is run)
> then return to the workbook and export another group for example:
>
> I need Belgium & Netherlands to be exported (values only) to one workbook,
> then from the original workbook I need Spain & Switzerland exported to
> another fresh workbook, so in the end I have 3 files, one original, one with
> Bel & Neth, and the other with Spa & Swi. I also need the data to be exported
> as values only.
>
> Currently I have the code below but it errors on workbook("Test").Select
>
> Sheets(Array("Netherland", "Belgium")).Select
> ActiveWindow.SelectedSheets.Copy
>
> Workbook("Test").Select
>
> Any help much appreciated
From: Louise on 20 May 2010 11:05
Hi Jacob - Thank you for responding, "Test" is my original workbook, I
presume after 'exporting' the 2 sheets Netherlands and Belgium, my macro
would then have to reselect / make active the main/original workbook to then
export the next 2 grouped sheets Spain & Switzerland?

Thanks for your help!

"Jacob Skaria" wrote:

> I didnt get what workbook "Test" is? If you want to select or save the new
> workbook try the below....
>
> Dim wb As Workbook
> Sheets(Array("Netherland", "Belgium")).Copy
> Set wb = ActiveWorkbook
> wb.SaveAs "c:\test.xls"
>
>
> --
> Jacob (MVP - Excel)
>
>
> "Louise" wrote:
>
> > Hi All,
> >
> > I need some help please...
> >
> > I have a workbook (which due to many pivots and formulae is massive), I need
> > a macro to export groups of sheets to new workbooks (each time it is run)
> > then return to the workbook and export another group for example:
> >
> > I need Belgium & Netherlands to be exported (values only) to one workbook,
> > then from the original workbook I need Spain & Switzerland exported to
> > another fresh workbook, so in the end I have 3 files, one original, one with
> > Bel & Neth, and the other with Spa & Swi. I also need the data to be exported
> > as values only.
> >
> > Currently I have the code below but it errors on workbook("Test").Select
> >
> > Sheets(Array("Netherland", "Belgium")).Select
> > ActiveWindow.SelectedSheets.Copy
> >
> > Workbook("Test").Select
> >
> > Any help much appreciated
 | 
Pages: 1
Prev: Cell colour format according to date
Next: Autocomplete Custom Functions and functions arguments