macro not working

[Tim Dolan]

I've been trying to get a macro to work. It is a macro that prints the
previous week's data on a data sheet.
When I click on the macro, I get the message to enter week number.
When I do that I get this error message. "run-time error 424"
object required
when I hit the debug button I get this
Sub PrintOut()
' ActiveSheet.Unprotect
Set myWeek = Application.InputBox("Select the week number to print", , ,
, , , , 8)
Set myRange = Range(myRange.Offset(0, 6),
myRange.SpecialCells(xlLastCell)).EntireColumn.Hidden = True
If myRange.Column <> 10 Then
Range(myRange.Offset(0, -1), Cells(3, 10)).EntireColumn.Hidden = True
End If
ActiveSheet.PrintPreview
' ActiveSheet.PrintOut
Cells.EntireColumn.Hidden = False
ActiveSheet.Protect
End Sub

Would someone help me get this macro to work?

 

[JE McGimpsey]

Since you never set myRange, your

Set myRange = Range(myRange.Offset(0, 6)...

throws an error.

What is myRange? Is it the same as myWeek (which you don't do anything
with)? If so, you'd benefit from putting Option Explicit at the top of
your module, so that you'd have to Dim each variable, and the compiler
would have picked up the non-Dim of myRange or myWeek.

 

[Tim Dolan]

JE,
Thanks for the reply but.... I don't know enough to understand what
you said. Do I have to put something into that part that I cut and pasted to
you?

 

[Dave Peterson]

Try this against a copy of your workbook...

Change this line:
Set myWeek = Application.InputBox(.....
to
Set myRange = Application.InputBox(.....

If it does what you want when you test it, woohoo. If not, post back.

 

[Tim Dolan]

Dave,
It didn't make a difference. BTW the line below the MYweek line had
an arrow next to it and it was highlighted in yellow.
Does that give and direction?

 

[Dave Peterson]

I really don't have a guess--so here it is! (Since I don't know what you want
the macro to do, all I can say is that this compiled ok:

Option Explicit

Sub PrintOut()
Dim myRange As Range

' ActiveSheet.Unprotect
Set myRange = Application.InputBox("Select the week number to print", _
Type:=8)
Set myRange = Range(myRange.Offset(0, 6), _
myRange.SpecialCells(xlLastCell))
myRange.EntireColumn.Hidden = True
If myRange.Column <> 10 Then
Range(myRange.Offset(0, -1), Cells(3, 10)).EntireColumn.Hidden _
= True
End If
ActiveSheet.PrintPreview
' ActiveSheet.PrintOut
Cells.EntireColumn.Hidden = False
ActiveSheet.Protect
End Sub

(I don't know if it does what you want.)

 

[Tim Dolan]

Hi Dave,
I tried to reply to you via e-mail but I couldn't remove the
xspam.
I don't mind sending you the whole spread sheet if that will help. Won't
that help make it easier.?
The macro permitted me to include the previous weeks stats with
the summary.
Tim fm Ct

 

[Dave Peterson]

I don't like to open workbooks--too many things can go wrong.

And besides, you'd still have to describe what you want the code to do and if
I'm not around, someone else will jump in with a suggestion--so you'll get a
quicker response.

Hi Dave,
I tried to reply to you via e-mail but I couldn't remove the
xspam.
I don't mind sending you the whole spread sheet if that will help. Won't
that help make it easier.?
The macro permitted me to include the previous weeks stats with
the summary.
Tim fm Ct

 

[Tim Dolan]

Dave,
I have an excel spread sheet, work sheet, data sheet whatever its
called to keep track of a seniors 8-ball pool league that I run.
Six teams (five on a team) play each week. Each player plays five
games. I keep the stats each week and give the players a summary each week.
Weekly stats consist of #games played, # of balls made, # of games
won, # of break and runs etc.
The summary provides the totals and the player averages but it does
not give them the actual weekly stats.
The macro that I had gave me the option of printing the previous weeks
stats or any week that I chose on the same sheet as the summary.
Also, why the concern over me sending you the work sheet? What can
happen?
Tim
PS I don't think anyone else is interested in this, because no one else
seems to reply.

 

[Tim Dolan]

Dave,
Wow! I just got a "brain storm". I put the summary up on the web
every week.
Check out www.ctpoolleague.com and click on the "shooters standings".
Tim fm Ct

 

[Dave Peterson]

Workbooks can have macros in them. Not all macros are written by nice people.
(Even though yours might not do any damage, it's still my general rule not to
open other people's workbooks.)

Dave,
Wow! I just got a "brain storm". I put the summary up on the web
every week.
Check out www.ctpoolleague.com and click on the "shooters standings".
Tim fm Ct

 

[Tim Dolan]

Please help me, if you can.
I had a macro that worked on a spread sheet that I use to keep stats for a
seniors 8-ball league.
A person helped me with my stats and really made it better. However, the
macro that I had that allowed me to print previous weeks stats no longer
works.
I haven't been able to describe the macro very well and apparently
worksheets can contain viruses.
Here's my question.
I have Norton Internet Security 2005. How can I make my worksheet safe for
this NG so I can get your help?
Thanks in advance,
Tim fm CT

 

[Dave Peterson]

You may get a volunteer to open your file.

But if you describe your problem in plain text using specific terms, you may get
others to help, too.

Please help me, if you can.
I had a macro that worked on a spread sheet that I use to keep stats for a
seniors 8-ball league.
A person helped me with my stats and really made it better. However, the
macro that I had that allowed me to print previous weeks stats no longer
works.
I haven't been able to describe the macro very well and apparently
worksheets can contain viruses.
Here's my question.
I have Norton Internet Security 2005. How can I make my worksheet safe for
this NG so I can get your help?
Thanks in advance,
Tim fm CT