macro to copy and edit then delete a worksheet

I have created a macro that will open the workbook, print preview, copy a
worksheet. After I create the copy I want to edit the contents then print a
range. Upon exiting I want to delete the copy I created save and close the
workbook. I have been able to do everything except do the editing in the
worksheet before the macro prints, deletes and closes. Any suggestions

Are you copying the worksheet to a new workbook? If yes, then you can do all
your editing, print it and just close that workbook. Since that workbook hasn't
been saved, you don't need to delete it.

Kind of...

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet

Set curWks = ActiveSheet

curWks.Copy 'to a new workbook

Set newWks = ActiveSheet

With newWks
.Range("a1").Value = "hi there!"
.Range("a1:b99").PrintOut preview:=True
.Parent.Close savechanges:=False
End With

End Sub

(I used preview:=true to save some paper.)



lschuh wrote:
>
> I have created a macro that will open the workbook, print preview, copy a
> worksheet. After I create the copy I want to edit the contents then print a
> range. Upon exiting I want to delete the copy I created save and close the
> workbook. I have been able to do everything except do the editing in the
> worksheet before the macro prints, deletes and closes. Any suggestions

--

Dave Peterson

I am copying a worksheet (within a workbook). Basically I am creating a copy
of an existing worksheet. (eg: "audioinput", "audioinput(1)"
The reason I want to copy the existing worksheet is so that I can edit and
make the cosmetics better before I print out a hard copy. After my print
out, I then want to delete the "audioinput(1)". I was thinking I would have
to save the worksheet but all that is necessary before closing is to remove
the extra "audioinput(1)" or the next time I run the macro I will have an
"audioinput(2)" and so on.....

"Dave Peterson" wrote:

> Are you copying the worksheet to a new workbook? If yes, then you can do all
> your editing, print it and just close that workbook. Since that workbook hasn't
> been saved, you don't need to delete it.
>
> Kind of...
>
> Option Explicit
> Sub testme()
> Dim curWks As Worksheet
> Dim newWks As Worksheet
>
> Set curWks = ActiveSheet
>
> curWks.Copy 'to a new workbook
>
> Set newWks = ActiveSheet
>
> With newWks
> .Range("a1").Value = "hi there!"
> .Range("a1:b99").PrintOut preview:=True
> .Parent.Close savechanges:=False
> End With
>
> End Sub
>
> (I used preview:=true to save some paper.)
>
>
>
> lschuh wrote:
> >
> > I have created a macro that will open the workbook, print preview, copy a
> > worksheet. After I create the copy I want to edit the contents then print a
> > range. Upon exiting I want to delete the copy I created save and close the
> > workbook. I have been able to do everything except do the editing in the
> > worksheet before the macro prints, deletes and closes. Any suggestions
>
> --
>
> Dave Peterson
>

Why not just copy it to a new workbook????

but...

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet

Set curWks = ActiveSheet 'worksheets("audioinput")

curWks.Copy _
before:=worksheets(1)

Set newWks = ActiveSheet

With newWks
.Range("a1").Value = "hi there!"
.Range("a1:b99").PrintOut preview:=True
application.displayalerts = false
.delete
application.displayalerts = true
End With

End Sub

lschuh wrote:
>
> I am copying a worksheet (within a workbook). Basically I am creating a copy
> of an existing worksheet. (eg: "audioinput", "audioinput(1)"
> The reason I want to copy the existing worksheet is so that I can edit and
> make the cosmetics better before I print out a hard copy. After my print
> out, I then want to delete the "audioinput(1)". I was thinking I would have
> to save the worksheet but all that is necessary before closing is to remove
> the extra "audioinput(1)" or the next time I run the macro I will have an
> "audioinput(2)" and so on.....
>
> "Dave Peterson" wrote:
>
> > Are you copying the worksheet to a new workbook? If yes, then you can do all
> > your editing, print it and just close that workbook. Since that workbook hasn't
> > been saved, you don't need to delete it.
> >
> > Kind of...
> >
> > Option Explicit
> > Sub testme()
> > Dim curWks As Worksheet
> > Dim newWks As Worksheet
> >
> > Set curWks = ActiveSheet
> >
> > curWks.Copy 'to a new workbook
> >
> > Set newWks = ActiveSheet
> >
> > With newWks
> > .Range("a1").Value = "hi there!"
> > .Range("a1:b99").PrintOut preview:=True
> > .Parent.Close savechanges:=False
> > End With
> >
> > End Sub
> >
> > (I used preview:=true to save some paper.)
> >
> >
> >
> > lschuh wrote:
> > >
> > > I have created a macro that will open the workbook, print preview, copy a
> > > worksheet. After I create the copy I want to edit the contents then print a
> > > range. Upon exiting I want to delete the copy I created save and close the
> > > workbook. I have been able to do everything except do the editing in the
> > > worksheet before the macro prints, deletes and closes. Any suggestions
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

I can't get the syntax to work
curwks.copy_
before:=worksheets(1)

"Dave Peterson" wrote:

> Why not just copy it to a new workbook????
>
> but...
>
> Option Explicit
> Sub testme()
> Dim curWks As Worksheet
> Dim newWks As Worksheet
>
> Set curWks = ActiveSheet 'worksheets("audioinput")
>
> curWks.Copy _
> before:=worksheets(1)
>
> Set newWks = ActiveSheet
>
> With newWks
> .Range("a1").Value = "hi there!"
> .Range("a1:b99").PrintOut preview:=True
> application.displayalerts = false
> .delete
> application.displayalerts = true
> End With
>
> End Sub
>
> lschuh wrote:
> >
> > I am copying a worksheet (within a workbook). Basically I am creating a copy
> > of an existing worksheet. (eg: "audioinput", "audioinput(1)"
> > The reason I want to copy the existing worksheet is so that I can edit and
> > make the cosmetics better before I print out a hard copy. After my print
> > out, I then want to delete the "audioinput(1)". I was thinking I would have
> > to save the worksheet but all that is necessary before closing is to remove
> > the extra "audioinput(1)" or the next time I run the macro I will have an
> > "audioinput(2)" and so on.....
> >
> > "Dave Peterson" wrote:
> >
> > > Are you copying the worksheet to a new workbook? If yes, then you can do all
> > > your editing, print it and just close that workbook. Since that workbook hasn't
> > > been saved, you don't need to delete it.
> > >
> > > Kind of...
> > >
> > > Option Explicit
> > > Sub testme()
> > > Dim curWks As Worksheet
> > > Dim newWks As Worksheet
> > >
> > > Set curWks = ActiveSheet
> > >
> > > curWks.Copy 'to a new workbook
> > >
> > > Set newWks = ActiveSheet
> > >
> > > With newWks
> > > .Range("a1").Value = "hi there!"
> > > .Range("a1:b99").PrintOut preview:=True
> > > .Parent.Close savechanges:=False
> > > End With
> > >
> > > End Sub
> > >
> > > (I used preview:=true to save some paper.)
> > >
> > >
> > >
> > > lschuh wrote:
> > > >
> > > > I have created a macro that will open the workbook, print preview, copy a
> > > > worksheet. After I create the copy I want to edit the contents then print a
> > > > range. Upon exiting I want to delete the copy I created save and close the
> > > > workbook. I have been able to do everything except do the editing in the
> > > > worksheet before the macro prints, deletes and closes. Any suggestions
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

I spoke too soon. That line works when you type it in correctly.
Can I ask what the
..range("a1").value = "hi xxx" is supposed to do.

"Dave Peterson" wrote:

> Why not just copy it to a new workbook????
>
> but...
>
> Option Explicit
> Sub testme()
> Dim curWks As Worksheet
> Dim newWks As Worksheet
>
> Set curWks = ActiveSheet 'worksheets("audioinput")
>
> curWks.Copy _
> before:=worksheets(1)
>
> Set newWks = ActiveSheet
>
> With newWks
> .Range("a1").Value = "hi there!"
> .Range("a1:b99").PrintOut preview:=True
> application.displayalerts = false
> .delete
> application.displayalerts = true
> End With
>
> End Sub
>
> lschuh wrote:
> >
> > I am copying a worksheet (within a workbook). Basically I am creating a copy
> > of an existing worksheet. (eg: "audioinput", "audioinput(1)"
> > The reason I want to copy the existing worksheet is so that I can edit and
> > make the cosmetics better before I print out a hard copy. After my print
> > out, I then want to delete the "audioinput(1)". I was thinking I would have
> > to save the worksheet but all that is necessary before closing is to remove
> > the extra "audioinput(1)" or the next time I run the macro I will have an
> > "audioinput(2)" and so on.....
> >
> > "Dave Peterson" wrote:
> >
> > > Are you copying the worksheet to a new workbook? If yes, then you can do all
> > > your editing, print it and just close that workbook. Since that workbook hasn't
> > > been saved, you don't need to delete it.
> > >
> > > Kind of...
> > >
> > > Option Explicit
> > > Sub testme()
> > > Dim curWks As Worksheet
> > > Dim newWks As Worksheet
> > >
> > > Set curWks = ActiveSheet
> > >
> > > curWks.Copy 'to a new workbook
> > >
> > > Set newWks = ActiveSheet
> > >
> > > With newWks
> > > .Range("a1").Value = "hi there!"
> > > .Range("a1:b99").PrintOut preview:=True
> > > .Parent.Close savechanges:=False
> > > End With
> > >
> > > End Sub
> > >
> > > (I used preview:=true to save some paper.)
> > >
> > >
> > >
> > > lschuh wrote:
> > > >
> > > > I have created a macro that will open the workbook, print preview, copy a
> > > > worksheet. After I create the copy I want to edit the contents then print a
> > > > range. Upon exiting I want to delete the copy I created save and close the
> > > > workbook. I have been able to do everything except do the editing in the
> > > > worksheet before the macro prints, deletes and closes. Any suggestions
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

You said that you needed to change something on that copied sheet. I figured
you were gonna do that via code.

I used
.range("a1").value = "Hi there"
to show you where you'd add your code that changes something.



lschuh wrote:
>
> I spoke too soon. That line works when you type it in correctly.
> Can I ask what the
> .range("a1").value = "hi xxx" is supposed to do.
>
> "Dave Peterson" wrote:
>
> > Why not just copy it to a new workbook????
> >
> > but...
> >
> > Option Explicit
> > Sub testme()
> > Dim curWks As Worksheet
> > Dim newWks As Worksheet
> >
> > Set curWks = ActiveSheet 'worksheets("audioinput")
> >
> > curWks.Copy _
> > before:=worksheets(1)
> >
> > Set newWks = ActiveSheet
> >
> > With newWks
> > .Range("a1").Value = "hi there!"
> > .Range("a1:b99").PrintOut preview:=True
> > application.displayalerts = false
> > .delete
> > application.displayalerts = true
> > End With
> >
> > End Sub
> >
> > lschuh wrote:
> > >
> > > I am copying a worksheet (within a workbook). Basically I am creating a copy
> > > of an existing worksheet. (eg: "audioinput", "audioinput(1)"
> > > The reason I want to copy the existing worksheet is so that I can edit and
> > > make the cosmetics better before I print out a hard copy. After my print
> > > out, I then want to delete the "audioinput(1)". I was thinking I would have
> > > to save the worksheet but all that is necessary before closing is to remove
> > > the extra "audioinput(1)" or the next time I run the macro I will have an
> > > "audioinput(2)" and so on.....
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Are you copying the worksheet to a new workbook? If yes, then you can do all
> > > > your editing, print it and just close that workbook. Since that workbook hasn't
> > > > been saved, you don't need to delete it.
> > > >
> > > > Kind of...
> > > >
> > > > Option Explicit
> > > > Sub testme()
> > > > Dim curWks As Worksheet
> > > > Dim newWks As Worksheet
> > > >
> > > > Set curWks = ActiveSheet
> > > >
> > > > curWks.Copy 'to a new workbook
> > > >
> > > > Set newWks = ActiveSheet
> > > >
> > > > With newWks
> > > > .Range("a1").Value = "hi there!"
> > > > .Range("a1:b99").PrintOut preview:=True
> > > > .Parent.Close savechanges:=False
> > > > End With
> > > >
> > > > End Sub
> > > >
> > > > (I used preview:=true to save some paper.)
> > > >
> > > >
> > > >
> > > > lschuh wrote:
> > > > >
> > > > > I have created a macro that will open the workbook, print preview, copy a
> > > > > worksheet. After I create the copy I want to edit the contents then print a
> > > > > range. Upon exiting I want to delete the copy I created save and close the
> > > > > workbook. I have been able to do everything except do the editing in the
> > > > > worksheet before the macro prints, deletes and closes. Any suggestions
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

it does not copy the worksheet. it goes right to the print preview.

"Dave Peterson" wrote:

> Why not just copy it to a new workbook????
>
> but...
>
> Option Explicit
> Sub testme()
> Dim curWks As Worksheet
> Dim newWks As Worksheet
>
> Set curWks = ActiveSheet 'worksheets("audioinput")
>
> curWks.Copy _
> before:=worksheets(1)
>
> Set newWks = ActiveSheet
>
> With newWks
> .Range("a1").Value = "hi there!"
> .Range("a1:b99").PrintOut preview:=True
> application.displayalerts = false
> .delete
> application.displayalerts = true
> End With
>
> End Sub
>
> lschuh wrote:
> >
> > I am copying a worksheet (within a workbook). Basically I am creating a copy
> > of an existing worksheet. (eg: "audioinput", "audioinput(1)"
> > The reason I want to copy the existing worksheet is so that I can edit and
> > make the cosmetics better before I print out a hard copy. After my print
> > out, I then want to delete the "audioinput(1)". I was thinking I would have
> > to save the worksheet but all that is necessary before closing is to remove
> > the extra "audioinput(1)" or the next time I run the macro I will have an
> > "audioinput(2)" and so on.....
> >
> > "Dave Peterson" wrote:
> >
> > > Are you copying the worksheet to a new workbook? If yes, then you can do all
> > > your editing, print it and just close that workbook. Since that workbook hasn't
> > > been saved, you don't need to delete it.
> > >
> > > Kind of...
> > >
> > > Option Explicit
> > > Sub testme()
> > > Dim curWks As Worksheet
> > > Dim newWks As Worksheet
> > >
> > > Set curWks = ActiveSheet
> > >
> > > curWks.Copy 'to a new workbook
> > >
> > > Set newWks = ActiveSheet
> > >
> > > With newWks
> > > .Range("a1").Value = "hi there!"
> > > .Range("a1:b99").PrintOut preview:=True
> > > .Parent.Close savechanges:=False
> > > End With
> > >
> > > End Sub
> > >
> > > (I used preview:=true to save some paper.)
> > >
> > >
> > >
> > > lschuh wrote:
> > > >
> > > > I have created a macro that will open the workbook, print preview, copy a
> > > > worksheet. After I create the copy I want to edit the contents then print a
> > > > range. Upon exiting I want to delete the copy I created save and close the
> > > > workbook. I have been able to do everything except do the editing in the
> > > > worksheet before the macro prints, deletes and closes. Any suggestions
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Actually, it copies the worksheet, changes A1 (look for that "Hi There" in the
printpreview window), then deletes the worksheet after you dismiss the
printpreview window.



lschuh wrote:
>
> it does not copy the worksheet. it goes right to the print preview.
>
> "Dave Peterson" wrote:
>
> > Why not just copy it to a new workbook????
> >
> > but...
> >
> > Option Explicit
> > Sub testme()
> > Dim curWks As Worksheet
> > Dim newWks As Worksheet
> >
> > Set curWks = ActiveSheet 'worksheets("audioinput")
> >
> > curWks.Copy _
> > before:=worksheets(1)
> >
> > Set newWks = ActiveSheet
> >
> > With newWks
> > .Range("a1").Value = "hi there!"
> > .Range("a1:b99").PrintOut preview:=True
> > application.displayalerts = false
> > .delete
> > application.displayalerts = true
> > End With
> >
> > End Sub
> >
> > lschuh wrote:
> > >
> > > I am copying a worksheet (within a workbook). Basically I am creating a copy
> > > of an existing worksheet. (eg: "audioinput", "audioinput(1)"
> > > The reason I want to copy the existing worksheet is so that I can edit and
> > > make the cosmetics better before I print out a hard copy. After my print
> > > out, I then want to delete the "audioinput(1)". I was thinking I would have
> > > to save the worksheet but all that is necessary before closing is to remove
> > > the extra "audioinput(1)" or the next time I run the macro I will have an
> > > "audioinput(2)" and so on.....
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Are you copying the worksheet to a new workbook? If yes, then you can do all
> > > > your editing, print it and just close that workbook. Since that workbook hasn't
> > > > been saved, you don't need to delete it.
> > > >
> > > > Kind of...
> > > >
> > > > Option Explicit
> > > > Sub testme()
> > > > Dim curWks As Worksheet
> > > > Dim newWks As Worksheet
> > > >
> > > > Set curWks = ActiveSheet
> > > >
> > > > curWks.Copy 'to a new workbook
> > > >
> > > > Set newWks = ActiveSheet
> > > >
> > > > With newWks
> > > > .Range("a1").Value = "hi there!"
> > > > .Range("a1:b99").PrintOut preview:=True
> > > > .Parent.Close savechanges:=False
> > > > End With
> > > >
> > > > End Sub
> > > >
> > > > (I used preview:=true to save some paper.)
> > > >
> > > >
> > > >
> > > > lschuh wrote:
> > > > >
> > > > > I have created a macro that will open the workbook, print preview, copy a
> > > > > worksheet. After I create the copy I want to edit the contents then print a
> > > > > range. Upon exiting I want to delete the copy I created save and close the
> > > > > workbook. I have been able to do everything except do the editing in the
> > > > > worksheet before the macro prints, deletes and closes. Any suggestions
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson