macro not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim Dolan - 25 Feb 2005 21:05 GMT

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?

Reply to this Message

JE McGimpsey - 25 Feb 2005 21:36 GMT

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.

> 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.
[quoted text clipped - 18 lines]
>
> Would someone help me get this macro to work?

Reply to this Message

Tim Dolan - 26 Feb 2005 18:26 GMT

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?

> Since you never set myRange, your
>
[quoted text clipped - 30 lines]
>>
>> Would someone help me get this macro to work?

Reply to this Message

Dave Peterson - 26 Feb 2005 23:26 GMT

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.

> JE,
> Thanks for the reply but.... I don't know enough to understand what
[quoted text clipped - 35 lines]
> >>
> >> Would someone help me get this macro to work?

Signature

Dave Peterson

Reply to this Message

Tim Dolan - 01 Mar 2005 03:11 GMT

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?

> Try this against a copy of your workbook...
>
[quoted text clipped - 48 lines]
>> >>
>> >> Would someone help me get this macro to work?

Reply to this Message

Dave Peterson - 01 Mar 2005 03:44 GMT

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

> Dave,
> It didn't make a difference. BTW the line below the MYweek line had
[quoted text clipped - 57 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Tim Dolan - 01 Mar 2005 15:32 GMT

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

>I really don't have a guess--so here it is! (Since I don't know what you
>want
[quoted text clipped - 91 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 01 Mar 2005 20:37 GMT

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
[quoted text clipped - 103 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Tim Dolan - 03 Mar 2005 19:03 GMT

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.

>I don't like to open workbooks--too many things can go wrong.
>
[quoted text clipped - 121 lines]
>> >
>> > Dave Peterson

Reply to this Message

Tim Dolan - 03 Mar 2005 19:13 GMT

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

>I don't like to open workbooks--too many things can go wrong.
>
[quoted text clipped - 121 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 03 Mar 2005 19:57 GMT

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
[quoted text clipped - 130 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Tim Dolan - 09 Mar 2005 04:11 GMT

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

> Workbooks can have macros in them. Not all macros are written by nice
> people.
[quoted text clipped - 144 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 09 Mar 2005 12:24 GMT

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
[quoted text clipped - 161 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message