Copying Data to another sheet Automatically and in real time.

[SJ789]

Hi Guys new around here, I do get confused with this kind of stuff so if I make a hiccup please feel free to correct me or ask me any questions to get a better understanding. I feel like it's going to be way easier to explain what I do and maybe show some examples. So here goes nothing.

I'm what some might call a advantage gamble/Match Bettor. I do this daily and have been doing so for many years now. I record every bet I make within a spreadsheet. I do this under 1 sheet, It's actually fine the way it is. It's simple and easier to understand. Given that there are many types of bets you can place there are plenty of types you can do. (In simple terms, You may have a Free bet or you maybe your doing some casino stuff).

My end goal is to be able to keep using my spreadsheet the way I have been doing for the past few years but I want my spreadsheet to sort my data in real time without having to click like a macro to make it work, So that when I enter a record given the type of bet I place it will be copied to another sheet depending on the type. I managed to set it up properly in Google Sheets but I don't think you can do it the same way which is why I'm here.

Now I'll try and explain with a few images cause I'm sure I messed that up.


So here is my basic sheet, There are other headings but they really don't need to be seen for this.

So under the heading "Type" are a list of types of bets I will make. In this case for this type of bet it's a "Casino". Now what I want is for every record that under the "Type" Column that say's Casino I want to Copy (Not remove) the whole Row to the Casino sheet and keep on listing them in real time without having to click a button. Then I would want the same to every Type of bet named "Freebet" and copy it to the Freebet sheet. Again I don't want to remove the data out of my main betting sheet.

Here is what I had done on google sheets this was last year,

So what I did was create another sheet basically a carbon copy of my main sheet and it would copy everything and would update in real time when I entered a new entry. From this, I created a another sheet for the "Type" of bet I wanted to filter. To do this I use the formula =query(Sheet17!A8:N, "Select * Where D = 'Casino' ") .......... (Where D, is just the "Type" Column is. So anytime Column D = Casino it would copy the that Row of data to the Casino Sheet.

I'm not sure if you are still with me if you are Thanks for reading this. I've probably confused you a little, I have a knack of explaining things pretty bad :D ... Most of the time if I'm pointed in the right direction I can work it out, I've searched for this in just basic terms but I'm not really sure what I'm looking for.

Again Thanks!

 

[SJ789]

Also here is a short video of what I basically done in google sheets

 

[vcoolio]

Hello SJ789,

You could use a Worksheet_Change event code placed in the "Betting Sheet" module to do this, as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(4)) Is Nothing Then Exit Sub  '----> Assumes Column D to hold the criteria. Change to suit.
If Target.Cells.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)

Application.ScreenUpdating = True

End Sub

I'm assuming that you will have a drop down data validation list in each cell in Column D with all the bet types listed. Ensure that these types are spelled exactly the same as the destination worksheets.

Please note that this will only work in Excel not Google Sheets.

To implement this code in Excel:-

- Right click on the "Betting Sheet" tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Each time you make a selection from the list in any cell drop down, the relevant row of data will be immediately transferred to the relevant worksheet. No button is required to do this.

I hope that this helps.

Cheerio,
vcoolio.

 

[SJ789]

Hi thanks for your reply I will give this a go now!