Excel help!

[wesleyw]

Okay I need some advice I have a spreadsheet with the results of a season of football (well we're only into the first 10 games but you get the idea) I want a macro that will pull this weeks fixtures into another sheet from a pull/drop down menu how would I do this?

 

 

Wes

[Steve21]

So what exactly do you want done? This weeks fixtures = The results?

 

What is the drop down menu showing? etc etc

 

Steve

[wesleyw]

Yes sorry for not providing more info, This weeks fixtures = the results, I'd like to create a drop down menu showing each of the possible weekdays games could be played on (I.E. 3/4/2011, 10/4/2011) selecting this will then list in a sheet on the games played at that time. I have a filter on a sheet that does this but I wanted to copy the info to a printable league table so that the table would show and the results of that weeks matches would show underneath these of course are on different sheets?

 

 

Wes

[Steve21]

Any chance you could upload your original sheet? Would be easier to just edit a copy of that Or it is "private" as such?

 

Steve

[wesleyw]

Not private really no. I'll just upload it gimme a minute.

[wesleyw]

Ok here it is:

Sun_League_Table.xls

[Steve21]

It's only a rough copy to see if it's how you want it etc

 

But:

Sun_League_Table - Copy.xls

 

Basically, The button on "Input Area A" will copy and list all the matches for the Date in M2 (Can be changed to dropdown box later) onto Sheet 1.

 

(Currently no deleting, so have to manually do it)

 

But is that the kind of thing you meant? Just neater/tidier/formatted/sparkly!

 

Steve

[wesleyw]

Yeah something like that but with some of the columns hidden and one column between the home and away teams combining the scores, fantastic though Steve thanks for that!

 

 

Wes

[wesleyw]

How would I do the same thing using the printable results sheet and putting it into the league table sheet under each division?

 

Thanks

 

 

Wes

[Steve21]

Hey, Sorry forgot to post it.

 

Sun_League_Table - Copy2.xls

 

Did your formatting style, and removed the need to hide columns etc. Also deletes on button press.

 

Steve

[wesleyw]

Thanks Steve thats great!

 

 

Wes

[Steve21]

Thanks Steve thats great!

 

 

Wes

 

If you right click the button, and view macro code, you can work out if there's anything you want changed.

 

It's pretty standard logic

 

Just automating, copy/pastes, between sheets etc.

 

But yeah, shout if you get stuck

 

Steve

[wesleyw]

I'm looking at changing the B and C on the macro to display I and J instead as the excel sheet names are just placeholder the real names are completely different how would I change this round?

 

Wes

[Steve21]

I'm looking at changing the B and C on the macro to display I and J instead as the excel sheet names are just placeholder the real names are completely different how would I change this round?

 

Wes

 

How'd you mean? If you want I and J to show aka Team X it'll still need a sheet name, if you're moving it onto another sheet.

 

And because of the way you wrote the macro's it needs a reference to what sheet/cells its getting.

 

*Edit - if the only issue is sheet names, can't you just rename the sheet name in the macro?

 

Steve

[wesleyw]

Sheet name is fine I just want to put I and J into Columns A and B on Sheet 1 and not use A and B? Is that makes sense the main reason for this is the team names that will be put in that could be anything not just team 1 2 3 or a b c?

 

 

Wes

[Steve21]

Sheet name is fine I just want to put I and J into Columns A and B on Sheet 1 and not use A and B? Is that makes sense the main reason for this is the team names that will be put in that could be anything not just team 1 2 3 or a b c?

 

 

Wes

 

Honestly, I'm completly lost.

 

You want column sheet1(I&J) into sheet1(A&B).

 

Do you mean you want I and J into "where the current teams are?"

 

aka like this:

Sun_League_Table - Latest.xls

 

So it'll copy the actual team name? That'll work as I changed the macro "aslong" as you use names, or directed macros. Using ones like you got in there currently break as you'll see on the ones I didn't edit.

 

Explain if I got that wrong, but if it's right hurrah

 

Steve

[wesleyw]

It's close sorry about the wording from earlier. Is there anyway of copying the value from the cells I and J on "input area a" into the cells you have done on sheet 1 for the referenced cells as well or isn't this possible?

 

Wes

[wesleyw]

Managed to get it working the way I wanted in the end thanks for all your help Steve! I just changed the macro from

 

Range("I" & CStr(LSearchRow), "I" & CStr(LSearchRow)).Select

Selection.Copy

Sheets("sheet1").Select

Range("B" & CStr(LCopyToRow), "B" & CStr(LCopyToRow)).Select

ActiveSheet.Paste

 

to Range("sheet1!B" & CStr(LCopyToRow)).Value = Range("'input area a'!I" & CStr(LSearchRow)).Value

 

 

Thanks again Steve!

 

Wes