Using MS Excel 2003

I have 2 workbooks open in my Excel window.

Wb1 catches the exported data from QuickBooksPro (in doing this, QB
overwrites any data that is on a worksheet already, precluding my
dumping the export directly onto my template. Ugh!!

Wb2 is my template and is linked to Wb1 to get the data into the cells
to generate my report from the template without messing with my col/row
headings, formulas etc.

Now, when I close Wb1, I want to delete the ws, which will always be
sheet1. I need help with the syntax on this please.

Then, when I close Wb2, I want to dump all of the linked info so my
template is empty and ready for the next time I need to use it. Again,
this is where I need help with the syntax.

Any guidance you can offer will be much appreciated.
Thanks
Joanne

Hi Joanne

If I understand you right, you don't want to save any of the data in
the two workbooks. Assuming that is right, I would save both workbooks
as templates.

File > Save as > Save as type > choose template.

//Per

On 28 Dec., 20:55, Joanne <RobJ...@comcast.net> wrote:
> Using MS Excel 2003
>
> I have 2 workbooks open in my Excel window.
>
> Wb1 catches the exported data from QuickBooksPro (in doing this, QB
> overwrites any data that is on a worksheet already, precluding my
> dumping the export directly onto my template. Ugh!!
>
> Wb2 is my template and is linked to Wb1 to get the data into the cells
> to generate my report from the template without messing with my col/row
> headings, formulas etc.
>
> Now, when I close Wb1, I want to delete the ws, which will always be
> sheet1. I need help with the syntax on this please.
>
> Then, when I close Wb2, I want to dump all of the linked info so my
> template is empty and ready for the next time I need to use it. Again,
> this is where I need help with the syntax.
>
> Any guidance you can offer will be much appreciated.
> Thanks
> Joanne

hi
are you going to use WB1 again? If you are you could just close without
saving which would in effect clear the sheet.
another way....
Private Sub Workbook_BeforeClose() 'or BeforeSave()
Sheets("Sheet1").Cells.ClearContents
End Sub
this doesn't delete the sheet, it justs clears all the cells.

as to clearing the template, you will have to do a surgical delete.
Private Sub Workbook_BeforeClose()
Sheets("Sheet1").Range(A1, B2, C3, D4, E5:G10, A5, B6, C7, _
D8, E12:G20, F3, H4).ClearContents
End Sub

The range addresses above were just made up for example purposes only and
have nothing to do with your template. you will have to find all of the range
addresses in your template that you want data cleared out and add them to the
above sample code.

using xp here.
Regards
FSt1
"Joanne" wrote:

> Using MS Excel 2003
>
> I have 2 workbooks open in my Excel window.
>
> Wb1 catches the exported data from QuickBooksPro (in doing this, QB
> overwrites any data that is on a worksheet already, precluding my
> dumping the export directly onto my template. Ugh!!
>
> Wb2 is my template and is linked to Wb1 to get the data into the cells
> to generate my report from the template without messing with my col/row
> headings, formulas etc.
>
> Now, when I close Wb1, I want to delete the ws, which will always be
> sheet1. I need help with the syntax on this please.
>
> Then, when I close Wb2, I want to dump all of the linked info so my
> template is empty and ready for the next time I need to use it. Again,
> this is where I need help with the syntax.
>
> Any guidance you can offer will be much appreciated.
> Thanks
> Joanne
>
>

Thank you - this info does it for me I think
Joanne

FSt1 wrote:

> hi
> are you going to use WB1 again? If you are you could just close without
> saving which would in effect clear the sheet.
> another way....
> Private Sub Workbook_BeforeClose() 'or BeforeSave()
> Sheets("Sheet1").Cells.ClearContents
> End Sub
> this doesn't delete the sheet, it justs clears all the cells.
>
> as to clearing the template, you will have to do a surgical delete.
> Private Sub Workbook_BeforeClose()
> Sheets("Sheet1").Range(A1, B2, C3, D4, E5:G10, A5, B6, C7, _
> D8, E12:G20, F3, H4).ClearContents
> End Sub
>
> The range addresses above were just made up for example purposes only and
> have nothing to do with your template. you will have to find all of the range
> addresses in your template that you want data cleared out and add them to the
> above sample code.
>
> using xp here.
> Regards
> FSt1
> "Joanne" wrote:
>
> > Using MS Excel 2003
> >
> > I have 2 workbooks open in my Excel window.
> >
> > Wb1 catches the exported data from QuickBooksPro (in doing this, QB
> > overwrites any data that is on a worksheet already, precluding my
> > dumping the export directly onto my template. Ugh!!
> >
> > Wb2 is my template and is linked to Wb1 to get the data into the cells
> > to generate my report from the template without messing with my col/row
> > headings, formulas etc.
> >
> > Now, when I close Wb1, I want to delete the ws, which will always be
> > sheet1. I need help with the syntax on this please.
> >
> > Then, when I close Wb2, I want to dump all of the linked info so my
> > template is empty and ready for the next time I need to use it. Again,
> > this is where I need help with the syntax.
> >
> > Any guidance you can offer will be much appreciated.
> > Thanks
> > Joanne
> >
> >