• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Needing to move data from sheet1 to sheet2, if conditions are met

H

heybata

New Member
I have 1 table on sheet1 called Universe
I have 2 tables on sheet2 called Positions Players, and Pitchers Tables.

I am wanting to move the info from Universe sheet if the name in Column (Owner), is Rog, to Sheet2 in the appropriate Table based on Column(Pos) in Universe, if the player is a RP, or a SP, they would go to the Pitchers Table, If the player is a Position Player, they would go to the Positions Players Table.

Pitchers = SP, RP(Pitchers Table)
Positions Players = 1B, SS, 2B, 3B, OF, C.(Position Players Table)

I was told to try VBA, but I know absolutely nothing about VBA.
Any help would be Greatly Appreciated.
Thanks
 

Attachments

  • TestExample.xlsx
    26.1 KB · Views: 4
Hi Heybata

When shifting data I always try and use Excel’s built features. This avoids looping constructs.
Here is an example.

Code: 
Option Explicit
 
Sub MoveMe()
Dim rng As Range
 
Set rng = Sheet1.Range("A4", Sheet1.Range("K" & Rows.Count).End(xlUp))
Sheet2.[A5:Q500].ClearContents
'Part A
Sheet1.AutoFilterMode = False
rng.AutoFilter 10, "Rog"
rng.AutoFilter 5, "RP", xlOr, "SP"
Range("A5:B100, D5:I100").Copy Sheet2.[J5]
'Part B
rng.AutoFilter 5, "<>" & "RP", xlAnd, "<>" & "SP"
Range("A5:B100, D5:I100").Copy Sheet2.[A5]
 
[A4].AutoFilter
End Sub

The workbook attached to show workings

Take care
Smallman
 

Attachments

  • TestExampleSm.xlsm
    41.3 KB · Views: 2
Thank You Both for your time and results, both seem to handle all i asked.

I do have a couple Questions:
This workbook will grow to several more sheets, all these sheets will have a Owner attached in the universe, Example, Rog, Who, What, When, How, etc.
Also every sheet will have tables for each Roster, Example: MLB, AAA, AA, A, RFA, These tables will be split as the above you experienced in the first post, example: Position Players, Pitchers.
1 - My question is will both these scripts be handle these actions?
2 - Will this be a challenge to add the extra sheets and extra actions required for the final results?
Thanks again for your time and helping others who like me are totally new to VBA or excel.
heybata.
 

Attachments

  • TestExampleRog.xlsx
    363.4 KB · Views: 3
@heybata

My code may not work for your current set. What data will go to different sheet from sheet1? Say who sheet will receive what data and on what criteria?
What is the role behind owner in different sheet?

Regards,
 
Sorry i should have explained that better.

Same as the example in the first post, if Owner column has the owners name in that column, it will go to the owners name sheet.
also in the Roster column that will determine what table it goes to in that owners sheet.
I think i got that right.
Basically the Owners column will tell what sheet to go to.
and the Roster column will tell what Table in that owners sheet.
Thanks
 
Ok i changed some data on Sheet1 universe.
I added 14 different owners, and those owners will have 1 pitcher and 1 Position Player for each table on that owners sheet.
I hope that helps in explaining what i want to do if possible.
Thanks
 

Attachments

  • TestExampleRog2.xlsx
    282.7 KB · Views: 1
@heybata

Two doubt:

1. There are some rows with blank Owner's. What you want to do wit those rows?
2. What is the role of roster in shifting data?

Regards,
 
1 - I want all rows to stay the same on the universe sheet, all i want to do is when a owner adds a player then i will go to the spreadsheet and put that owners name in the Owners column then i want that info in the row transferred or copied and pasted on the owners sheet, the blank cells in the owners column or any other column will stay the same on the universe sheet.

2 - This is a Major league baseball league i am trying to get running, In the League we have 5 rosters, MLB, AAA, AA, A, and the RFA roster that owners drop players and they are responsible for that player or players Salary each year.
there will be at least MLB players roster counting pitchers and position players about 25 players on the MLB roster.
There will be 25 more players on the AAA roster.
There will be 30 Players on the AA roster.
There will be 10 players on the A roster.
The RFA Roster could be as many as 20 or 25 depending on the owners activity with his rosters.

3 - I thought it would be better to make a table for pitchers and position players on owners sheets, so they could be more organized, but if this don't work i might have to do something different.
4 - I have tried Formulas with excel but nothing seem to work, i was told that it couldn't be done with excel formulas, and was told VBA was the path to go on.
Thanks again for your time.
heybata
 
I hope to leave the rows where they are on the Universe sheet, i just want the info from the rows took to their owners sheet and tables.
I always want the universe sheet to stay in tact.
If this can't be done with out moving the rows, then i will have to go another direction, because i need the rows to stay on the universe sheet.
Thanks, and hope this helps.
 
Here is a league spreadsheet of another kind, that seems to work, but for some reason the formulas won't work for this spreadsheet.
It may be too big to upload, but i will try.
Thanks, enjoy your day.
 

Attachments

  • BODUniverse2Sept2014.xlsx
    929.2 KB · Views: 2
No I haven't tried Pivot tables.
I don't care what I have to use.
Just the simplest way would be good, i know i have probably frustrated you somewhat, I guess maybe I should take some learning lessons.
Your help is grateful, Thanks
 
With pivot tables you can generate different sheets "Owner" wise. The problem is that you will get one table for pitchers and other players which you can filter on the sheets itself and can be use for further analysis.

The benefit, you jest need to press Refresh All once to update all the sheets if you change main data.

If you like the idea I can attach a sample file.

Regards,
 
Thanks again
Sure I like the idea, so what your saying is there would be 1 table on each owners sheet, then they could just filter the table on their sheet to see all players?
If so then i am game.
Thanks
heybata
 
Thanks Somendra Misra
I am just trying to figure out the pivot tables in general.

Just so you know i will be using a data scrape to fill the Player rater column, that will come from a external site upload, that will give the top 800 players value in the player rater column, the blanks will be filled with $0.00, in that column as well, not sure if that will effect anything.

1 - Also how do i get the Roster column in the table, or maybe how to use it where it is(the top left corner), because all owners will need this to filter from MLB, AAA, AA, A, RFA, to see the players on each roster.

2 - Also how to add new rows(new players) to the table, and will this update the owners tab if that player needs to be added to his table.

If these actions can be done, then we might have a solution, i think.
Thanks for your time and efforts.
heybata
 
@heybata

If the purpose of these tables is just to able Owner's to see their teams, take prints of different combinations, I would suggest you to keep the Roaster where it is, it will enable the owner to put filters and see different roasters.

To add more player in the main table, go to main table last row;last column and press tab. This will create a new row in the table; there you can enter the data. Go to Owner sheet, click on any cell inside pivot and press Refresh All from the options. See if the data is updated.

Regards,
 
Ok Thanks again
I got all to work.

1 - What does the blank tab do, does it need to be their?
2 - So sheet1 updates sheet2, once refresh is selected?
3 - So you can update everything from sheet2 once sheet1 is updated?
4 - So where is all the formulas stored, or is their any at all?
5 - I hope that is all the questions, just feeling my way around the Pivot tables and all they can do.
Thanks
heybata
 
You must log in or register to reply here.
Back
Top