cycle through multiple sheets

[ttowncorp]

I'll try to explain this as easy as possible because I know it can be done just don't know how to put it all together. I have a template that I created to track 1600 vehicles. only problem i don't want to have 1500 sheets. i want to be able to select the vehicle from my dropdown list. so basically when i go to my drop down list of vehicles listed on B4, and i select vehicle 2100 i want the whole sheet to change. i know i will need a save tab as well. so in the middle of the page has a picture of a vehicle. d10 has a dropdown list of " yes, no, cable is cut, cable is missing and c9 is the description of that list "is the harnesses connected". k4 another drop down list " yes, no, part is missing" and discription is on j3 saying is the part mounted properly. I27 another drop down list saying "yes, no, green light blinking, red lights blinking, both lights blinking and the discription is on H26 "is the power on" B4 is the vehicle list which is this.

2100
2101
2102
2104
2105
2107
2108
2109
2111
2112
2113
2114
2115
2116
2119
2120
2121
2122
2123
2124
2126
2127
2129
2130
2132
2133
2134
2135
2137
2139
2140
2141
2142
2144
2145
2146
2147
2148
2150
2152
2153
2154
2155
2159
2160
2161
2162
2163
2164
2164
2165
2166
2167
2168
2169
2170
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2190
2191
2192
2193
2194
2195
2196
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2212
2213
2214
2215
2217
2218
2220
2221
2223
2225
2225
2226
2227
2229
2230
2231
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2401
2402
2403
2404
2405
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2464
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2831
2832
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
3087
3751
3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
3766
3767
3768
3770
4203
4204
4205
4207
4207
4210
4211
4213
4216
4216
4217
4218
4220
4221
4223
4224
4225
4225
4226
4227
4235
4239
4240
4241
4246
4248
4249
4249
4253
4257
4258
4259
4263
4263
4264
4265
4266
4266
4267
4268
4270
4270
4271
4271
4272
4273
4273
4274
4277
4281
4284
4284
4291
4291
4293
4295
4297
4298
4299
4327
5301
5302
5303
5304
5305
5306
5307
5308
5309
5310
5311
5312
5313
5314
5315
5316
5317
5318
5319
5320
5321
5401
5402
5403
5404
5405
5406
5407
5408
5409
5410
5411
5412
5413
5414
5415
5416
5417
5418
5419
5420
5421
5422
5431
5432
5433
5434
5435
5436
5437
5438
5439
5440
5441
5442
5443
5444
5445
5446
5447
5448
5449
5450
5451
5452
5461
5462
5463
5464
5465
5466
5467
5468
6001
6002
6003
6004
6005
6006
6007
6008
6009
6010
6011
6012
6013
6014
6015
6016
6017
6018
6019
6020
6021
6022
6023
6024
6025
6026
6027
6028
6029
6030
6031
6032
6033
6034
6035
6036
6037
6038
6039
6040
6041
6042
6043
6044
6045
6046
6047
6048
6049
6050
6101
6102
6103
6104
6105
6106
6107
6108
6109
6110
6111
6112
6113
6114
6115
6116
6117
6118
6119
6120
6121
6122
6123
6124
6125
6126
6127
6128
6129
6130
6131
6132
6133
6134
6135
6136
6137
6138
6139
6140
6141
6142
6143
6144
6145
6146
6147
6148
6149
6150
6151
6152
6153
6154
6155
6156
6157
6158
6159
6160
6161
6162
6163
6164
6165
6166
6167
6168
6169
6170
6171
6172
6173
6174
6175
6176
6177
6178
6179
6180
6181
6182
6183
6184
6185
6186
6187
6188
6189
6190
6191
6192
6193
6194
6195
6196
6197
6198
6199
6200
6201
6202
6203
6204
6205
6207
6208
6209
6210
6211
6212
6213
6214
6215
6216
6217
6301
6302
6303
6304
6305
6306
6307
6308
6309
6310
6311
6312
6313
6314
6315
6316
6317
6318
6319
6320
6321
6322
6323
6324
6325
6326
6327
6328
6329
6330
6331
6332
6333
6334
6335
6337
6338
6339
6340
6341
6342
6343
6344
6345
6346
6347
6348
6349
6350
6351
6352
6353
6354
6355
6356
6357
6358
6359
6360
6361
6362
6363
6364
6366
6368
6369
6370
6371
6372
6373
6374
6375
6376
6377
6378
6379
6380
6381
6382
6383
6384
6385
6386
6387
6388
6389
6390
6391
6392
6393
6394
6395
6396
6397
6398
6399
6400
6401
6402
6403
6404
6405
6406
6407
6408
6409
6410
6411
6412
6413
6414
6415
6416
6417
6418
6419
6420
6421
6422
6423
6424
6425
6426
6427
6428
6429
6430
6431
6432
6433
6434
6435
6436
6437
6438
6439
6440
6441
6442
6443
6444
6445
6446
6447
6448
6449
6450
6451
6452
6453
6454
6455
6456
6457
6458
6459
6460
6461
6462
6463
6464
6465
6466
6467
6468
6469
6470
6471
6472
6473
6474
6475
6476
6477
6478
6479
6480
6481
6482
6483
6484
6485
6486
6488
6489
6490
6491
6492
6493
6494
6495
6496
6497
6498
6499
6500
6501
6502
6503
6504
6505
6506
6507
6508
6509
6510
6511
6512
6513
6514
6515
6516
6517
6518
6519
6520
6521
6522
6523
6524
6525
6526
6527
6528
6529
6530
6531
6532
6533
6534
6535
6536
6537
6538
6539
6540
6541
6542
6543
6544
6545
6546
6547
6548
6549
6550
6551
6552
6553
6554
6555
6556
6557
6558
6559
6560
6561
6562
6563
6564
6565
6566
6567
6568
6569
6570
6571
6572
6573
6574
6575
6576
6577
6578
6579
6580
6581
6582
6583
6584
6585
6586
6587
6588
6589
6590
6591
6592
6593
6594
6595
6596
6597
6598
6599
6600
6601
6602
6603
6604
6605
6606
6607
6608
6609
7001
7002
7003
7004
7005
7006
7007
7008
7009
7010
7011
7012
7013
7014
7015
7016
7017
7018
7019
7020
7021
7022
7023
7024
7025
7026
7027
7028
7029
7030
7031
7032
7033
7034
7035
7036
7037
7038
7039
7040
7041
7042
7043
7044
7045
7046
7047
7048
7049
7050
7051
7052
7053
7054
7055
7056
7057
7058
7059
7060
7061
7062
7063
7064
7065
7066
7067
7068
7069
7070
7071
7072
7073
7074
7075
7076
7077
7078
7079
7080
7081
7082
7083
7084
7085
7086
7087
7088
7089
7090
7091
7092
7093
7094
7095
7096
7097
7098
7099
7100
7101
7102
7103
7104
7105
7106
7107
7108
7109
7110
7111
7112
7113
7114
7115
7116
7117
7118
7119
7120
7121
7122
7123
7124
7125
7126
7127
7128
7129
7130
7131
7132
7133
7134
7135
7136
7137
7138
7139
7140
7141
7142
7143
7144
7145
7146
7147
7148
7149
7150
7151
7152
7153
7154
7155
7156
7157
7158
7159
7160
7161
7162
7163
7164
7165
7166
7167
7168
7169
7170
7171
7172
7173
7174
7175
7176
7177
7178
7179
7180
7181
7182
7183
7184
7185
7186
7187
7188
7189
7190
7191
7192
7193
7194
7195
7196
7197
7198
7199
7200
7201
7202
7203
7204
7205
7206
7207
7208
7209
7210
7211
7212
7213
7214
7215
7216
7217
7218
7219
7220
7221
7222
7223
7224
7225
7226
7227
7228
7229
7230
7231
7232
7233
7234
7235
7236
7237
7238
7240
7241
7242
7243
7244
7245
7246
7247
7249
7250
7251
7252
7253
7254
7255
7256
7257
7258
7259
7260
7261
7262
7263
7264
7265
7266
7267
7268
7269
7270
7271
7272
8001
8002
8003
8004
8005
8006
8007
8008
8009
8010
8011
8013
8014
8015
8016
8017
8018
8019
8020
8021
8022
8023
8024
8025
8026
8027
8028
8029
8030
8031
8032
8033
8034
8035
8036
8037
8038
8039
8040
8041
8042
8043
8044
8045
8046
8047
8048
8049
8050
8051
8052
8053
8054
8055
8056
8057
8058
8059
8060
8061
8062
8063
8064
8065
8066
8067
8068
8069
8070
8071
8072
8073
8074
8075
8077
8078
8079
8080
8081
8082
8083
8084
8085
8086
8087
8088
8089
8090
8091
8092
8093
8094
8095
8096
8097
8098
8099
8100
8101
8102
8103
8104
8105
C011
C012
C013
C014
C015
C016

<colgroup><col></colgroup><tbody>
</tbody>

 

[CalcSux78]

Wow.. Had to scroll forever!

So I'm a bit confused, but what it sounds like is:

• Your workbook might be just a Form and a Table with all the data
• When the vehichle changes in B4, you'd like the form to update to reflect the info for that selection

If I'm on the right track, I'd recommend a Worksheet_Change event watching for changes in B4 of the form sheet.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rVeh As Range
Dim R As Long

'clear form here


Set rVeh = Intersect(Target, Range("B4"))

If Not rVeh Is Nothing Then
    'Match statement to identify the row of the matching vehichle
    'let R = matching row number
    
    [D10] = Sheet2.Cells(R, n1)
    [C9] = Sheet2.Cells(R, n2)
    '... continue till form filled
End If

End Sub

 

[ttowncorp]

yes I would like form to update to reflect when i change vehicle numbers. will this code here work for me if i just copy this. I don't know how the coding work in excel as much.

 

[CalcSux78]

The code is a basic outline. To get it to work, more info about how the tables, sheets, columns are setup, where are the fields to be filled located, etc...

 

[ttowncorp]

in my first post i added were the cells and description are on my sheet. as in size wise i'm using cells A-M29 is the full size. i have my list off to the right in O,P,Q,R, and S CELL. the vehicle numbers i have not put into a list just as yet but will most likely put it under the T cell then of course hide all that. the following link is from my dropbox and what i'm working on, let me know if the link works

https://www.dropbox.com/s/wg543hzn4dos3ap/drive camera inspection.png?dl=0

 

[CalcSux78]

I would setup a new sheet in the workbook to function as a database table:

Excel 2010
	A	B	C	D	E
1	Bus	Q1	Q2	Q3	Q4
2	2100	Yes	Yes	No	No
3	2101	Yes	Yes	Yes	Both lights blinking
New Sheet

You would continue this to house the responses for each Bus #.

From there, you would need to setup a Worksheet_Change event to Retrieve data when the bus# changes and sends the new data back to the database sheet when responses change.

You would also need to build a way to add to the list when the bus number is not found.

 

[ttowncorp]

https://www.dropbox.com/s/jvpthsv47e0hclb/bus tracking service sheet 3.12.15.xlsm?dl=0

attached is my original sheet that but i've had help with working on this one, and i know i can use the same type coding for this project here. some of this i can understand and some i don't. i just need to change a few things like in my new database, but what to change in the coding i'm not sure in this code

Option Compare Text 'ignore text case
Sub saveupdate()
'******
Dim x As Long
Dim y As Long
Dim Z As Long
Dim hit As Long
Dim targ As String
Dim dstrg As String
Dim slastrow As Long
Dim slastcol As Long
Dim rlastrow As Long
slastrow = Sheet1.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
slastcol = Sheet1.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column + 1
rlastrow = Sheet2.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
cnt = rlastrow + 1
'Re-enable screenupdating in case disabled
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.StatusBar = False
'Display wait for a moment
Application.StatusBar = "****Please Wait***** Macro processing"
'opitmize macro by disabling all processes that slow it down.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
targ = Sheet1.Range("D1")
'existing entry
For x = 5 To slastrow
hit = 0
dstrg = Sheet1.Cells(x, 2)
For y = 1 To rlastrow
If targ = Sheet2.Cells(y, 1) And dstrg = Sheet2.Cells(y, 2) Then
Sheet2.Cells(y, 1) = targ
Sheet2.Cells(y, 2) = Sheet1.Cells(x, 2)
For Z = 1 To slastcol
If Z >= 4 Then
Sheet2.Cells(y, Z) = Sheet1.Cells(x, Z)
End If
Next Z
hit = 1
End If
Next y
'new entry
If hit = 0 Then
Sheet2.Cells(cnt, 1) = targ
Sheet2.Cells(cnt, 2) = Sheet1.Cells(x, 2)
Sheet2.Cells(cnt, 3) = Sheet1.Cells(x, 3)
For Z = 1 To slastcol
If Z >= 4 Then
Sheet2.Cells(cnt, Z) = Sheet1.Cells(x, Z)
End If
Next Z
cnt = cnt + 1
End If
Next x
ActiveWorkbook.Save
MsgBox "Save Complete", vbInformation, "SAVED"
'Re-enable screenupdating (before END SUB)
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.StatusBar = False
End Sub

and my main sheet were all my information is on coding is
Option Compare Text 'ignore text case
Private Sub Worksheet_Change(ByVal Target As Range)
'******
Dim lastrow As Long
Dim lastcol As Long
Dim rng As String
Dim x As Long
Dim y As Long
Dim frng1 As String
Dim frng2 As String
cnt = 5 'first data row
lastrow = Sheet2.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lastcol = Sheet2.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
If Target.Address = "$D$1" Then
Sheet1.Range("A5:IV65536") = "" 'clear previous
rng = "A1:A" & lastrow
If IsNumeric(Application.Match(Sheet1.Range("D1"), Sheet2.Range(rng), 0)) Then
For x = 1 To lastrow
If Sheet2.Cells(x, 1) = Sheet1.Range("D1") Then
Sheet1.Cells(cnt, 2) = Sheet2.Cells(x, 2) 'id
Sheet1.Cells(cnt, 3) = Sheet2.Cells(x, 3) 'date
For y = 1 To lastcol
If y >= 4 Then
Sheet1.Cells(cnt, y) = Sheet2.Cells(x, y) 'other elements
End If
Next y
cnt = cnt + 1
End If
Next x
'sort
lastrow = Sheet1.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
frng1 = "B4:B" & lastrow
frng2 = "B4:E" & lastrow
Sheet1.Sort.SortFields.Clear
Sheet1.Sort.SortFields.Add Key:=Range(frng1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheet1.Sort
.SetRange Range(frng2)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'add issue count
For x = 5 To lastrow
Sheet1.Cells(x, 1) = x - 4
Next x


Else
MsgBox UCase(Sheet1.Range("D1")) & " No data has been entered for this bus at this current time.", vbCritical, "ALERT"
End If
End If
End Sub