Copying Data to another sheet Automatically and in real time.

SJ789

New Member
Joined
Jun 6, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
Screenshot_2.png

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,
Google.png


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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

SJ789

New Member
Joined
Jun 6, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Also here is a short video of what I basically done in google sheets
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Hello SJ789,

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

VBA Code:
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

New Member
Joined
Jun 6, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi thanks for your reply I will give this a go now!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,697
Messages
5,637,864
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top