Need help writing a conditional formula

[tamaranicole]

I'm not a complete n00b, but my excel/VBA programming skills are WA
rusty.

What I'm trying to do is create a spreadsheet set for each account rep
Sheet #1 will show their commisions broken down by category across al
12 months with a ytd total at the end. Sheet #2 should import JUST th
current month's commision numbers, place them in the right "boxes" an
then show the ytd total.

Any ideas?

Thanks in advance

 

[Bob Phillips]

Here is some code that assumes that the monthly data is in columns 1-12, YTD
in 13

Private Sub Worksheet_Activate()
Dim iMonth As Long

With ActiveSheet
.Cells.Clear
iMonth = Month(Date)
Worksheets("Sheet1").Columns(iMonth).Copy _
Destination:=.Columns(1)
Worksheets("Sheet1").Columns(13).Copy _
Destination:=.Columns(2)
End With
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[tamaranicole]

That just lost me the spreadsheet I was working on. *sigh* I'm sur
that means I did something completely wrong and its not the code... I'
going to guess it was supposed to input the information into a blan
worksheet.

I'm going to attach the real file so you can see what I'm trying to do
And if it matters - I'm doing this on excel 2001 for Mac. Right no
the only sheets with information are for GK. There's a yearly and
monthly. I've got the yearly formulas all set up... I just don't kno
how to bring the monthly number for each client under each header fro
the yearly sheet into the appropriate spot in the monthly sheet an
keep that monthly sheet current. We need to show June's numbers whe
its June, for example. This is how the guys know what they've earne
each month.

Thanks for your help

Attachment filename: commisionwks.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=58366

 

[Bob Phillips]

Apart from a couple of changes to the code, just below, it seems okay to me.
But there is one major problem, it doesn't work, I get a run time error 9.
This usually means it can't find the referenced object, in this case the
worksheet. When I do a ?Worksheets(1).Name in the immediate window, I get
Sheet1, not 'GK - Monthly' as I would have expected. Don't know if this is a
problem because you are Mac and I am Windows?

What exactly happens for you?

Private Sub Worksheet_Activate()
Dim iMonth As Long

With ActiveSheet
.Cells.Clear
iMonth = Month(Date) + 1
Worksheets("GK - Yearly").Columns(1).Copy _
Destination:=.Columns(1)
Worksheets("GK - Yearly").Columns(iMonth).Copy _
Destination:=.Columns(2)
Worksheets("GK - Yearly").Columns(14).Copy _
Destination:=.Columns(3)
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Hey, guess what? I downloaded your spreadsheet onto my machine, and it
worked fine(sic!).

I have also updated it so that you don't need to copy the code into each
sheet, as long as you use the strict naming convention .

If you give me your email address I can send it to you and see how you get
on.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Apart from a couple of changes to the code, just below, it seems okay to me.
But there is one major problem, it doesn't work, I get a run time error 9.
This usually means it can't find the referenced object, in this case the
worksheet. When I do a ?Worksheets(1).Name in the immediate window, I get
Sheet1, not 'GK - Monthly' as I would have expected. Don't know if this is a
problem because you are Mac and I am Windows?

What exactly happens for you?

Private Sub Worksheet_Activate()
Dim iMonth As Long

With ActiveSheet
.Cells.Clear
iMonth = Month(Date) + 1
Worksheets("GK - Yearly").Columns(1).Copy _
Destination:=.Columns(1)
Worksheets("GK - Yearly").Columns(iMonth).Copy _
Destination:=.Columns(2)
Worksheets("GK - Yearly").Columns(14).Copy _
Destination:=.Columns(3)
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[tamaranicole]

Thnaks so much Bob!

You can email me at tnelson AT wrk DOT co

 

[Bob Phillips]

It's on its way.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)