Need help writing a conditional formula
 

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

--
Message posted from http://www.ExcelForum.com

Need help writing a conditional formula
 

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 " wrote in
message ...
I'm not a complete n00b, but my excel/VBA programming skills are WAY
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 all
12 months with a ytd total at the end. Sheet #2 should import JUST the
current month's commision numbers, place them in the right "boxes" and
then show the ytd total.

Any ideas?

Thanks in advance!


---
Message posted from http://www.ExcelForum.com/

Need help writing a conditional formula
 

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
--
Message posted from http://www.ExcelForum.com

Need help writing a conditional formula
 

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 " wrote in
message ...
That just lost me the spreadsheet I was working on. *sigh* I'm sure
that means I did something completely wrong and its not the code... I'm
going to guess it was supposed to input the information into a blank
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 now
the only sheets with information are for GK. There's a yearly and a
monthly. I've got the yearly formulas all set up... I just don't know
how to bring the monthly number for each client under each header from
the yearly sheet into the appropriate spot in the monthly sheet and
keep that monthly sheet current. We need to show June's numbers when
its June, for example. This is how the guys know what they've earned
each month.

Thanks for your help!

Attachment filename: commisionwks.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=583668
---
Message posted from http://www.ExcelForum.com/

Need help writing a conditional formula
 

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)

"Bob Phillips" wrote in message
...
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 " wrote in
message ...
That just lost me the spreadsheet I was working on. *sigh* I'm sure
that means I did something completely wrong and its not the code... I'm
going to guess it was supposed to input the information into a blank
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 now
the only sheets with information are for GK. There's a yearly and a
monthly. I've got the yearly formulas all set up... I just don't know
how to bring the monthly number for each client under each header from
the yearly sheet into the appropriate spot in the monthly sheet and
keep that monthly sheet current. We need to show June's numbers when
its June, for example. This is how the guys know what they've earned
each month.

Thanks for your help!

Attachment filename: commisionwks.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=583668
---
Message posted from http://www.ExcelForum.com/

Need help writing a conditional formula
 

Thnaks so much Bob!

You can email me at tnelson AT wrk DOT co

--
Message posted from http://www.ExcelForum.com

Need help writing a conditional formula
 

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)

"tamaranicole " wrote in
message ...
Thnaks so much Bob!

You can email me at tnelson AT wrk DOT com


---
Message posted from http://www.ExcelForum.com/