macro to copy and edit then delete a worksheet

[Guest]

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]

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.)

 

[Guest]

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]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[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.

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]

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.

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

 

[Guest]

I got this to sort of do what I want by using one macro and then did my
editing. When I was done editing I invoked another macro to delete the
sheet. save, close. Cheated but what the heck. I never put the a1 "hi
there" in as all I wanted to do is delete rows I did not need just to clean
up the worksheet. I am trying to use it as a form. I am creating
audiometric (hearing) charts. All employees have more years of data then
others. I am allowing enough rows to be placeholders for years 1979 through
2005. Some employees have been at the company long enough to have data in
all the rows(26). Others will not. All the rows have referential
referencing from the worksheet (audiochart). By creating a copy of the
original (audiochart(1)) I can edit those rows not needed without wiping out
(audiochart) referencing. The referencing is coming from a
transferspreadsheet that is tied to an MSAccess macro. I hope that explains
what I was trying to do.

 

[Dave Peterson]

I never meant for you to actually put "hi there" in your real code. It was just
a place holder for the code that would do the editing.

In fact, if you could give the rules for what rows to delete, you could plop
that code into the module.

If the rules are too complex and they need a human to do it, breaking up the
macro into pieces is a perfect choice. (well, your macro could ask for the
range of rows to delete and delete them, but that's just a matter of choice.)

I got this to sort of do what I want by using one macro and then did my
editing. When I was done editing I invoked another macro to delete the
sheet. save, close. Cheated but what the heck. I never put the a1 "hi
there" in as all I wanted to do is delete rows I did not need just to clean
up the worksheet. I am trying to use it as a form. I am creating
audiometric (hearing) charts. All employees have more years of data then
others. I am allowing enough rows to be placeholders for years 1979 through
2005. Some employees have been at the company long enough to have data in
all the rows(26). Others will not. All the rows have referential
referencing from the worksheet (audiochart). By creating a copy of the
original (audiochart(1)) I can edit those rows not needed without wiping out
(audiochart) referencing. The referencing is coming from a
transferspreadsheet that is tied to an MSAccess macro. I hope that explains
what I was trying to do.

 

[Guest]

so I suppose to do that you would probably need a msg box? Maybe I could do
that when I get better with coding in Excel. It doesn't look that difficult,
I think more straight forward then other programs. Thanks for all your help.

 

[Dave Peterson]

Actually, you could use application.inputbox() to get the range (msgboxes
wouldn't work if you want the user to select the rows to delete).

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

Set curWks = ActiveSheet 'worksheets("audioinput")

curWks.Copy _
before:=Worksheets(1)

Set newWks = ActiveSheet

With newWks
.Activate
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox _
(Prompt:="Please select some cells to indicate " & _
"what rows should be deleted", Type:=8)
On Error GoTo 0
If myRng Is Nothing Then
'user hit cancel.
Else
If myRng.Parent.Name <> .Name Then
MsgBox "Please select it on the same sheet!"
Else
myRng.EntireRow.Delete
.Range("a1:b99").PrintOut preview:=True
End If
End If
'clean up that sheet

Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

End Sub

so I suppose to do that you would probably need a msg box? Maybe I could do
that when I get better with coding in Excel. It doesn't look that difficult,
I think more straight forward then other programs. Thanks for all your help.

 

[Dave Peterson]

ps. If you're doing more than deleting rows, I like your start, stop, and
restart solution much better.

so I suppose to do that you would probably need a msg box? Maybe I could do
that when I get better with coding in Excel. It doesn't look that difficult,
I think more straight forward then other programs. Thanks for all your help.