Move entries from one sheet to another

N

NewToTheWorld

New Member
Joined
Mar 25, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hey folks,

Looking to see if you guys can help me get this figured out, I'm hoping that there is a way to get data that is entered on one sheet to automatically move to another sheet based on a Yes/No in a column.

Screenshot below, if column O = Yes, then move the data from A through N to the sheet titled "master" which is identical looking to this sheet. Then I'm assuming I duplicate this for all of the other sheets below also?

1648227617661.png
 
Sort by date Sort by votes
Try:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Target
        Select Case rng.Value
            Case "Missed", "Overage", "Short"
                With Sheets("Master")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I know you might kill me after this one, I keep finding areas where I want to 'improve' it for simplicity.

Would it be possible to have it move data from Master to the other sheets based on what's in column B? I have added a list on column B with those, do I need that?

I.E. - If Column B contains "300" move to Sheet "San Jose"
I.E. - If Column B contains "302" move to sheet "Sacramento"
I.E. - If column B contains "303" move to sheet "Fresno"
I.E. - If column B contains "310" move to sheet "Reno"
I.E. - If column B contains "312" move to sheet "North Bay"

This is what I tried to achieve that, it's pulling data from any row that it sees the word "300" in. How do I make it look only in "B" for that?
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Target
        Select Case rng.Value
            Case "300"
                With Sheets("San Jose")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub

1648313341661.png
 
Upvote 0
Place this macro in the Master sheet code module:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Target
        Select Case rng.Value
            Case "300"
                With Sheets("San Jose")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            Case "302"
                With Sheets("Sacramento")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            Case "303"
                With Sheets("Fresno")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            Case "310"
                With Sheets("Reno")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            Case "312"
                With Sheets("North Bay")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub
Did you place the original macro in all the other sheet code modules?
 
Last edited:
Upvote 0
It seems to be grabbing random rows, this is in the Sacramento column. Everything in B should be 302.
1648480626751.png
 
Upvote 0
This part of the code:
VBA Code: 
Case "302"
                With Sheets("Sacramento")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
ensures that only values of "302" will be copied to the "Sacramento" sheet. Could you use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your Master sheet? It is hard to work with pictures.
 
Upvote 0
H&S Test1.xlsm
ABCDEFGHIJKLMNO
1TRANSFERRED FROMTRANSFERRED TOISO ORDER NUMBERVENDORORDERED ITEMDESCRIPTIONTRANSFER ROUTEORDERED DATEISO UNITS REQUESTEDWEIGHTCUSTOMER SALES ORDERCUSTOMER SALES ORDER DATECUST UNITSLOCATION NUMBERLOCATION NAME
2303300211234238Continental15509980000225/65R17 102H CON CROSS CONTACT LX25 FR295H3/29/2022 10:46 AM127.002112342373/29/2022 10:46 AM188563AMERICA'S TIRE-1350
3312300211218898Yokohama110133510225/65R17 102T YOK AVID S33B (33510)110133510298H3/29/2022 5:29 AM127.002112188973/29/2022 5:28 AM11095456CUTTER CDJR FIAT OF HONOLULU -- FF LYNDEN INTERNATIONAL
4312302211216158Cooper90000023940235/45R18 94V COO CS5 ULTRA TOURING297H3/28/2022 8:38 PM127.002112161573/28/2022 8:37 PM1458059SIERRA NEVADA TIRE & WHEEL
5302300211215515Continental15573310000265/35ZR22XL 102W CON EXTREMECONTACT DWS06 PLUS295H3/28/2022 8:06 PM126.962112155143/28/2022 8:05 PM123754FIVE POINTS TIRE
6302300211215321Michelin04778265/35ZR21XL (101Y) MIC PILOT SUPER SPORT ACT TO FM295H3/28/2022 7:59 PM126.902112152603/28/2022 7:58 PM188566AMERICA'S TIRE-1481
7312302211213822Falken28035938265/35ZR19XL 98(Y) FAL AZENIS FK510297H3/28/2022 7:12 PM126.902112138213/28/2022 7:11 PM166386PK TIRES PROS
8312302211237001Falken59000550225/55R18 98H FAL ZIEX ZE001 A/S297H3/29/2022 11:11 AM124.902112369003/29/2022 11:11 AM126812BIG O TIRES #040
9303302211237294BF Goodrich50840225/45R18XL 95V BFG ADVANTAGE CONTROL CPJ297H3/29/2022 11:15 AM124.492112372933/29/2022 11:14 AM188593AMERICA'S TIRE-1771
10302312211210840Ironman93013225/50R17 94V IRON iMOVE GEN2 AS299H3/28/2022 6:45 PM124.142112108393/28/2022 6:42 PM1263355SONOMA TIRES
11312300211216434Michelin11229235/50R17 96H MIC DEFENDER T+H MTP298H3/28/2022 8:55 PM123.942112164333/28/2022 8:55 PM11131490AMERICA'S TIRE-1983
12303300211203621Nexen14475NXK265/30ZR19XL 93W NEX N5000 PLUS295H3/28/2022 5:29 PM123.872112035593/28/2022 5:29 PM1469997WD GARAGE
13303302211198744BF Goodrich23353P205/70R14 93S BFG RADIAL T/A RWL297H3/28/2022 4:41 PM123.832111987413/28/2022 4:41 PM126413HEAUSER'S PLAZA TIRE #2
Master



---- Here is what's on the Sacramento sheet. I'm noticing that it's pulling data from A as well if it says 302 in it.
H&S Test1.xlsm
ABCDEFGHIJK
1TRANSFERRED FROMTRANSFERRED TOISO ORDER NUMBERVENDORORDERED ITEMDESCRIPTIONTRANSFER ROUTEORDERED DATEISO UNITS REQUESTEDWEIGHTCUSTOMER SALES ORDER
2312302211216158Cooper90000023940235/45R18 94V COO CS5 ULTRA TOURING297H3/28/2022 8:38 PM127.00211216157
3302300211215515Continental15573310000265/35ZR22XL 102W CON EXTREMECONTACT DWS06 PLUS295H3/28/2022 8:06 PM126.96211215514
4302300211215321Michelin04778265/35ZR21XL (101Y) MIC PILOT SUPER SPORT ACT TO FM295H3/28/2022 7:59 PM126.90211215260
5312302211213822Falken28035938265/35ZR19XL 98(Y) FAL AZENIS FK510297H3/28/2022 7:12 PM126.90211213821
6312302211237001Falken59000550225/55R18 98H FAL ZIEX ZE001 A/S297H3/29/2022 11:11 AM124.90211236900
7303302211237294BF Goodrich50840225/45R18XL 95V BFG ADVANTAGE CONTROL CPJ297H3/29/2022 11:15 AM124.49211237293
8302312211210840Ironman93013225/50R17 94V IRON iMOVE GEN2 AS299H3/28/2022 6:45 PM124.14211210839
9303302211198744BF Goodrich23353P205/70R14 93S BFG RADIAL T/A RWL297H3/28/2022 4:41 PM123.83211198741
10302312211234227Ironman93010235/45ZR17XL 97W IRON iMOVE GEN2 AS299H3/29/2022 10:46 AM123.10211234156
11302300211216068Continental15573000000245/40ZR18XL 97Y CON EXTREMECONTACT DWS06 PLUS295H3/28/2022 8:34 PM122.30211216067
12302312211236894Michelin77092215/55R16 93V MIC ENERGY SAVER A/S ECE299H3/29/2022 11:11 AM120.35211236893
13302300211211266Pirelli2246100205/45R17XL 88V PIR CINTURATO P7 ALL SEASON RUN FLAT(*)295H3/28/2022 6:53 PM120.30211211243
14302312211213433Falken28292575185/55R15 82V FAL SINCERA SN250 A/S299H3/28/2022 7:01 PM116.80211213430
15302303211233924Yokohama110187019T145/70D17 106M YOK TEMPORARY SPARE Y870298H3/29/2022 10:43 AM111.92211233920
Sacramento
 
Upvote 0
Try:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Target
        Select Case rng.Offset(, 1).Value
            Case "300"
                With Sheets("San Jose")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            Case "302"
                With Sheets("Sacramento")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            Case "303"
                With Sheets("Fresno")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            Case "310"
                With Sheets("Reno")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            Case "312"
                With Sheets("North Bay")
                    rng.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
The macro assumes that when you copy/paste data into the Master, the pasting is done into column A.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Checkbox - True - Moves Whole Row To Another Sheet
Replies
3
Views
73
MikeVol
MikeVol
R
  • Question
Help moving values based on cell values from one sheet to another
Replies
5
Views
173
Cubist
Cubist
T
  • Solved
Move data from one sheet to another automatically
Replies
3
Views
246
kevin9999
K
D
  • Solved
Move sheet to the end
Replies
1
Views
150
mumps
mumps
J
  • Question
Moving Data to Another Sheet Based on Dropdown Selection
2 3
Replies
27
Views
1K
t0ny84
T

Forum statistics

Threads
1,216,099
Messages
6,128,822
Members
449,470
Latest member
Subhash Chand

Share this page

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
Back
Top