Need help with Macro

D

DKRbella0814

Board Regular
Joined
Aug 10, 2008
Messages
155
I have a workbook which contains three worksheets

Data = worksheet 1
M03Map = worksheet 2
M03 = worksheet 3


I would like the main data entry sheet to feed both the M03Map and M03 (Calculations) worksheet.

Can I write a single macro to have the Data sheet feed both of the other sheets? This is what I have written thus far, but when I go to run the macro, the Run button is grayed out and I am not able to run it. I'm guessing that somewhere I violated some rules of the code.


Sub M03()
' Dim X As Integer
Dim N As Integer

Dim Data As Worksheet
Dim M03Map As Worksheet
Dim M03 As Worksheet

Set Data = Worksheets("Data")
Set M03Map = Worksheets("M03Map")
Set M03 = Worksheets("M03")

Interns.Activate

N = ActiveCell.Row

For X = 1 To 256
Select Case X
Case 1
M03Map.Cells(6, 3) = Data.Cells(N, 2) 'Date
Case 2
M03Map.Cells(6, 3) = Data.Cells(N, 4) 'Shift
Case 3
M03Map.Cells(18, 8) = Data.Cells(N, 5) 'Scheduler
Case 4
M03Map.Cells(20, 8) = Data.Cells(N, 6) 'Tech Support
Case 5
M03Map.Cells(22, 8) = Data.Cells(N, 7) 'S/u#1
Case 6
M03Map.Cells(24, 8) = Data.Cells(N, 8) 'S/u#2
Case 7
M03Map.Cells(26, 3) = Data.Cells(N, 9) '6S
Case 8
M03Map.Cells(22, 3) = Data.Cells(N, 11) 'Machine(1) Loader
Case 9
M03Map.Cells(23, 3) = Data.Cells(N, 12) 'Machine(1) Backup
Case 10
M03Map.Cells(24, 3) = Data.Cells(N, 13) 'Machine(1) Inspector
Case 11
M03Map.Cells(17, 3) = Data.Cells(N, 14) 'Job#1
Case 12
M03Map.Cells(18, 3) = Data.Cells(N, 15) 'W.O. Qty
Case 13
M03Map.Cells(19, 3) = Data.Cells(N, 16) 'Lot
Case 14
M03Map.Cells(20, 3) = Data.Cells(N, 17) 'Op
Case 15
M03Map.Cells(21, 3) = Data.Cells(N, 18) 'MPP
Case 16
M03Map.Cells(17, 5) = Data.Cells(N, 19) 'Job#2
Case 17
M03Map.Cells(18, 5) = Data.Cells(N, 20) 'W.O. Qty
Case 18
M03Map.Cells(18, 5) = Data.Cells(N, 21) 'Lot
Case 19
M03Map.Cells(18, 5) = Data.Cells(N, 22) 'Op
Case 20
M03Map.Cells(18, 5) = Data.Cells(N, 23) 'MPP
Case 21
M03Map.Cells(13, 8) = Data.Cells(N, 46) 'Machine(2) Loader
Case 22
M03Map.Cells(14, 8) = Data.Cells(N, 47) 'Machine(2) Backup
Case 23
M03Map.Cells(15, 8) = Data.Cells(N, 48) 'Machine(2) Inspector
Case 24
M03Map.Cells(8, 8) = Data.Cells(N, 49) 'Job#1
Case 25
M03Map.Cells(9, 8) = Data.Cells(N, 50) 'W.O. Qty
Case 26
M03Map.Cells(10, 8) = Data.Cells(N, 51) 'Lot
Case 27
M03Map.Cells(11, 8) = Data.Cells(N, 52) 'Op
Case 28
M03Map.Cells(12, 8) = Data.Cells(N, 53) 'MPP
Case 29
M03Map.Cells(8, 10) = Data.Cells(N, 54) 'Job#2
Case 30
M03Map.Cells(9, 10) = Data.Cells(N, 55) 'W.O. Qty
Case 31
M03Map.Cells(10, 10) = Data.Cells(N, 56) 'Lot
Case 32
M03Map.Cells(11, 10) = Data.Cells(N, 57) 'Op
Case 33
M03Map.Cells(12, 10) = Data.Cells(N, 58) 'MPP
Case 34
M03Map.Cells(6, 3) = Data.Cells(N, 81) 'Machine(3) Loader
Case 35
M03Map.Cells(6, 3) = Data.Cells(N, 82) 'Machine(3) Backup
Case 36
M03Map.Cells(6, 3) = Data.Cells(N, 83) 'Machine(3) Inspector
Case 37
M03Map.Cells(17, 13) = Data.Cells(N, 84) 'Job#1
Case 38
M03Map.Cells(18, 13) = Data.Cells(N, 85) 'W.O. Qty
Case 39
M03Map.Cells(19, 13) = Data.Cells(N, 86) 'Lot
Case 40
M03Map.Cells(20, 13) = Data.Cells(N, 87) 'Op
Case 41
M03Map.Cells(21, 13) = Data.Cells(N, 88) 'MPP
Case 42
M03Map.Cells(17, 15) = Data.Cells(N, 89) 'Job#2
Case 43
M03Map.Cells(18, 15) = Data.Cells(N, 90) 'W.O. Qty
Case 44
M03Map.Cells(18, 15) = Data.Cells(N, 91) 'Lot
Case 45
M03Map.Cells(19, 15) = Data.Cells(N, 92) 'Op
Case 46
M03Map.Cells(20, 15) = Data.Cells(N, 93) 'MPP
Case 47
M03Map.Cells(6, 3) = Data.Cells(N, 116) 'Machine(4) Loader
Case 48
M03Map.Cells(6, 3) = Data.Cells(N, 117) 'Machine(4) Backup
Case 49
M03Map.Cells(6, 3) = Data.Cells(N, 118) 'Machine(4) Inspector
Case 50
M03Map.Cells(28, 13) = Data.Cells(N, 119) 'Job#1
Case 51
M03Map.Cells(29, 13) = Data.Cells(N, 120) 'W.O. Qty
Case 52
M03Map.Cells(30, 13) = Data.Cells(N, 121) 'Lot
Case 53
M03Map.Cells(31, 13) = Data.Cells(N, 122) 'Op
Case 54
M03Map.Cells(32, 13) = Data.Cells(N, 123) 'MPP
Case 55
M03Map.Cells(28, 15) = Data.Cells(N, 124) 'Job#2
Case 56
M03Map.Cells(29, 15) = Data.Cells(N, 125) 'W.O. Qty
Case 57
M03Map.Cells(30, 15) = Data.Cells(N, 126) 'Lot
Case 58
M03Map.Cells(31, 15) = Data.Cells(N, 127) 'Op
Case 59
M03Map.Cells(32, 15) = Data.Cells(N, 128) 'MPP
Case 60
M03.Cells(1, 30) = Data.Cells(N, 2) 'Date
Case 61
M03.Cells(1, 1) = Data.Cells(N, 4) 'Shift
Case 62
M03.Cells(5, 1) = Data.Cells(N, 14) 'Job(1)
Case 63
M03.Cells(5, 2) = Data.Cells(N, 15) 'Lot
Case 64
M03.Cells(5, 3) = Data.Cells(N, 16) 'Op
Case 65
M03.Cells(5, 4) = Data.Cells(N, 17) 'MPP
Case 66
M03.Cells(5, 5) = Data.Cells(N, 18) 'W.O. Qty
Case 67
M03.Cells(5, 13) = Data.Cells(N, 25) '#Pcs-Time(1)
Case 68
M03.Cells(5, 18) = Data.Cells(N, 29) '#Pcs-Time(2)
Case 69
M03.Cells(5, 23) = Data.Cells(N, 33) '#Pcs-Time(3)
Case 70
M03.Cells(5, 28) = Data.Cells(N, 37) '#Pcs-Time(4)
Case 71
M03.Cells(9, 1) = Data.Cells(N, 19) 'Job(2)
Case 72
M03.Cells(9, 2) = Data.Cells(N, 20) 'Lot
Case 73
M03.Cells(9, 3) = Data.Cells(N, 21) 'Op
Case 74
M03.Cells(9, 4) = Data.Cells(N, 22) 'MPP
Case 75
M03.Cells(9, 5) = Data.Cells(N, 23) 'W.O. Qty
Case 76
M03.Cells(9, 13) = Data.Cells(N, 26) '#Pcs-Time(1)
Case 77
M03.Cells(9, 18) = Data.Cells(N, 30) '#Pcs-Time(2)
Case 78
M03.Cells(9, 23) = Data.Cells(N, 34) '#Pcs-Time(3)
Case 79
M03.Cells(9, 28) = Data.Cells(N, 38) '#Pcs-Time(4)
Case 80
M03.Cells(13, 13) = Data.Cells(N, 27) '#Pcs-Time(1)
Case 81
M03.Cells(13, 18) = Data.Cells(N, 31) '#Pcs-Time(2)
Case 82
M03.Cells(13, 23) = Data.Cells(N, 35) '#Pcs-Time(3)
Case 83
M03.Cells(13, 28) = Data.Cells(N, 39) '#Pcs-Time(4)
Case 84
M03.Cells(17, 1) = Data.Cells(N, 49) 'Job(1)
Case 85
M03.Cells(17, 2) = Data.Cells(N, 50) 'Lot
Case 86
M03.Cells(17, 3) = Data.Cells(N, 51) 'Op
Case 87
M03.Cells(17, 4) = Data.Cells(N, 52) 'MPP
Case 88
M03.Cells(17, 5) = Data.Cells(N, 53) 'W.O. Qty
Case 89
M03.Cells(17, 13) = Data.Cells(N, 60) '#Pcs-Time(1)
Case 90
M03.Cells(17, 18) = Data.Cells(N, 64) '#Pcs-Time(2)
Case 91
M03.Cells(17, 23) = Data.Cells(N, 68) '#Pcs-Time(3)
Case 92
M03.Cells(17, 28) = Data.Cells(N, 72) '#Pcs-Time(4)
Case 93
M03.Cells(21, 1) = Data.Cells(N, 54) 'Job(2)
Case 94
M03.Cells(22, 2) = Data.Cells(N, 55) 'Lot
Case 95
M03.Cells(23, 3) = Data.Cells(N, 56) 'Op
Case 96
M03.Cells(24, 4) = Data.Cells(N, 57) 'MPP
Case 97
M03.Cells(25, 5) = Data.Cells(N, 58) 'W.O. Qty
Case 98
M03.Cells(21, 13) = Data.Cells(N, 61) '#Pcs-Time(1)
Case 99
M03.Cells(21, 18) = Data.Cells(N, 65) '#Pcs-Time(2)
Case 100
M03.Cells(21, 23) = Data.Cells(N, 69) '#Pcs-Time(3)
Case 101
M03.Cells(21, 28) = Data.Cells(N, 73) '#Pcs-Time(4)
Case 102
M03.Cells(25, 13) = Data.Cells(N, 62) '#Pcs-Time(1)
Case 103
M03.Cells(25, 18) = Data.Cells(N, 66) '#Pcs-Time(2)
Case 104
M03.Cells(25, 23) = Data.Cells(N, 70) '#Pcs-Time(3)
Case 105
M03.Cells(25, 28) = Data.Cells(N, 74) '#Pcs-Time(4)
Case 106
M03.Cells(29, 1) = Data.Cells(N, 84) 'Job(1)
Case 107
M03.Cells(29, 2) = Data.Cells(N, 85) 'Lot
Case 108
M03.Cells(29, 3) = Data.Cells(N, 86) 'Op
Case 109
M03.Cells(29, 4) = Data.Cells(N, 87) 'MPP
Case 110
M03.Cells(29, 5) = Data.Cells(N, 88) 'W.O. Qty
Case 111
M03.Cells(29, 13) = Data.Cells(N, 95) '#Pcs-Time(1)
Case 112
M03.Cells(29, 18) = Data.Cells(N, 99) '#Pcs-Time(2)
Case 113
M03.Cells(29, 23) = Data.Cells(N, 103) '#Pcs-Time(3)
Case 114
M03.Cells(29, 28) = Data.Cells(N, 107) '#Pcs-Time(4)
Case 115
M03.Cells(33, 1) = Data.Cells(N, 89) 'Job(2)
Case 116
M03.Cells(33, 2) = Data.Cells(N, 90) 'Lot
Case 117
M03.Cells(33, 3) = Data.Cells(N, 91) 'Op
Case 118
M03.Cells(33, 4) = Data.Cells(N, 92) 'MPP
Case 119
M03.Cells(33, 5) = Data.Cells(N, 93) 'W.O. Qty
Case 120
M03.Cells(33, 13) = Data.Cells(N, 96) '#Pcs-Time(1)
Case 121
M03.Cells(33, 18) = Data.Cells(N, 100) '#Pcs-Time(2)
Case 122
M03.Cells(33, 23) = Data.Cells(N, 104) '#Pcs-Time(3)
Case 123
M03.Cells(33, 28) = Data.Cells(N, 108) '#Pcs-Time(4)
Case 124
M03.Cells(37, 13) = Data.Cells(N, 97) '#Pcs-Time(1)
Case 125
M03.Cells(37, 18) = Data.Cells(N, 101) '#Pcs-Time(2)
Case 126
M03.Cells(37, 23) = Data.Cells(N, 105) '#Pcs-Time(3)
Case 127
M03.Cells(37, 28) = Data.Cells(N, 109) '#Pcs-Time(4)
Case 128
M03.Cells(41, 1) = Data.Cells(N, 119) 'Job(1)
Case 129
M03.Cells(41, 2) = Data.Cells(N, 120) 'Lot
Case 130
M03.Cells(41, 3) = Data.Cells(N, 121) 'Op
Case 131
M03.Cells(41, 4) = Data.Cells(N, 122) 'MPP
Case 132
M03.Cells(41, 5) = Data.Cells(N, 123) 'W.O. Qty
Case 133
M03.Cells(41, 13) = Data.Cells(N, 130) '#Pcs-Time(1)
Case 134
M03.Cells(41, 18) = Data.Cells(N, 134) '#Pcs-Time(2)
Case 135
M03.Cells(41, 23) = Data.Cells(N, 138) '#Pcs-Time(3)
Case 136
M03.Cells(41, 28) = Data.Cells(N, 142) '#Pcs-Time(4)
Case 137
M03.Cells(45, 1) = Data.Cells(N, 124) 'Job(2)
Case 138
M03.Cells(45, 2) = Data.Cells(N, 125) 'Lot
Case 139
M03.Cells(45, 3) = Data.Cells(N, 126) 'Op
Case 140
M03.Cells(45, 4) = Data.Cells(N, 127) 'MPP
Case 141
M03.Cells(45, 5) = Data.Cells(N, 128) 'W.O. Qty
Case 142
M03.Cells(45, 13) = Data.Cells(N, 131) '#Pcs-Time(1)
Case 143
M03.Cells(45, 18) = Data.Cells(N, 135) '#Pcs-Time(2)
Case 144
M03.Cells(45, 23) = Data.Cells(N, 139) '#Pcs-Time(3)
Case 145
M03.Cells(45, 28) = Data.Cells(N, 143) '#Pcs-Time(4)
Case 146
M03.Cells(49, 13) = Data.Cells(N, 132) '#Pcs-Time(1)
Case 147
M03.Cells(49, 18) = Data.Cells(N, 136) '#Pcs-Time(2)
Case 148
M03.Cells(49, 23) = Data.Cells(N, 140) '#Pcs-Time(3)
Case 149
M03.Cells(49, 28) = Data.Cells(N, 144) '#Pcs-Time(4)

End Select
Next X


M03Map.Activate
M03.Activate
Data.Activate

End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Try changing the name of the macro so it doesn't look like a cell reference. Maybe Sub EMMM03()
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
More efficient code? Moving data
2
Replies
16
Views
673
mumps
mumps
G
  • Question Question
Why does #VALUE error in VBA function occur when creating a ReDim Matrix of Stock prices from a Named Range?
Replies
2
Views
96
Peter_SSs
Peter_SSs
S
  • Question Question
Help with combining 2 macros into 1
Replies
8
Views
269
MARK858
MARK858
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
134
montecarlo2012
montecarlo2012
A
  • Solved
VBA Case Error
Replies
2
Views
64
Ark68
A

Forum statistics

Threads
1,224,517
Messages
6,179,240
Members
452,898
Latest member
Capolavoro009

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