Need help with Macros Code

DKRbella0814

Board Regular
Joined
Aug 10, 2008
Messages
155
Within a workbook, I have (2) worksheets that are dedicated to data entry. The first worksheet, 'Data' is intended for scheduling of personnel for the cells of machines that are supposed to run on any given day. The 'ProductionData' worksheet is intended to capture the # of good parts run on each of the machines within a machining cell for 4 specific increments of time on any given shift.

Key information from the 'Data' worksheet is copied over to the 'ProductionData' worksheet with the use of formulas.


So, for example, on the 'Data' worksheet

Col B= Date
Col C = Active Cell (VCM, M01, M03) one of these is selected with a drop down feature
Col D = Shift (1,2, or 3)
Col L & M = for personnel scheduling
Col N-AB = Data on current running jobs for a specific machine within the selected cell of machines (i.e. M28 machine within the M01 cell)

The above layout is repeated for up to 7 machines that could be active within a particular cell of machines.

On the 'ProductionData' worksheet, I have carried over (with a one-to-one correspondence of the active row and column from 'Data' worksheet columns B-D such that anyone looking at the second spreadsheet would know that the data for the jobs run is for the same group of machines as the machining cell on the 'Data' spreadsheet.


I have written a macro to try and pull specific data points from both the 'Data' worksheet and 'ProductionData' worksheet in order to populate both a cell Map and Calculations sheet for the designated machining cell within the same workbook.

When I try to run the below program, it does not pull the correct data from either worksheet that I have designated ('Data' and 'ProductionData')

Is it possible to write code to pull from more than one sheet and paste the values into other active worksheets within the same workbook? If so, can someone help me figure out how to correct the code below?

Thanks so much in advance for your help!











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

Dim Data As Worksheet
Dim M01Map As Worksheet
Dim ProductionData As Worksheet
Dim M01 As Worksheet

Set Data = Worksheets("Data")
Set M01Map = Worksheets("M01Map")
Set ProductionData = Worksheets("ProductionData")
Set M01 = Worksheets("M01")

Data.Activate
ProductionData.Activate

N = ActiveCell.Row

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



Case 124
M01.Cells(28, 10) = ProductionData.Cells(N, 68) 'Total # Parts Job(1)
Case 125
M01.Cells(29, 10) = ProductionData.Cells(N, 69) 'Total # NCM Job(1)
Case 126
M01.Cells(27, 53) = ProductionData.Cells(N, 70) 'Run Time Period(1); Job(1)
Case 127
M01.Cells(29, 13) = ProductionData.Cells(N, 71) '# Parts Job(1)
Case 128
M01.Cells(27, 54) = ProductionData.Cells(N, 72) 'Run Time Period(2); Job(1)
Case 129
M01.Cells(29, 18) = ProductionData.Cells(N, 73) '# Parts Job(1)
Case 130
M01.Cells(27, 55) = ProductionData.Cells(N, 74) 'Run Time Period(3); Job(1)
Case 131
M01.Cells(29, 23) = ProductionData.Cells(N, 75) '# Parts Job(1)
Case 132
M01.Cells(27, 56) = ProductionData.Cells(N, 76) 'Run Time Period(4); Job(1)
Case 133
M01.Cells(29, 28) = ProductionData.Cells(N, 77) '# Parts Job(1)
Case 134
M01.Cells(32, 10) = ProductionData.Cells(N, 78) 'Total # Parts Job(2)
Case 135
M01.Cells(34, 10) = ProductionData.Cells(N, 79) 'Total # NCM Job(2)
Case 136
M01.Cells(31, 53) = ProductionData.Cells(N, 80) 'Run Time Period(1); Job(2)
Case 137
M01.Cells(33, 13) = ProductionData.Cells(N, 81) '# Parts Job(2)
Case 138
M01.Cells(31, 54) = ProductionData.Cells(N, 82) 'Run Time Period(2); Job(2)
Case 139
M01.Cells(33, 18) = ProductionData.Cells(N, 83) '# Parts Job(2)
Case 140
M01.Cells(31, 55) = ProductionData.Cells(N, 84) 'Run Time Period(3); Job(2)
Case 141
M01.Cells(33, 23) = ProductionData.Cells(N, 85) '# Parts Job(2)
Case 142
M01.Cells(31, 56) = ProductionData.Cells(N, 86) 'Run Time Period(4); Job(2)
Case 143
M01.Cells(33, 28) = ProductionData.Cells(N, 87) '# Parts Job(2)
Case 144
M01.Cells(36, 10) = ProductionData.Cells(N, 88) 'Total # Parts Job(3)
Case 145
M01.Cells(38, 10) = ProductionData.Cells(N, 89) 'Total # NCM Job(3)
Case 146
M01.Cells(35, 53) = ProductionData.Cells(N, 90) 'Run Time Period(1); Job(3)
Case 147
M01.Cells(37, 13) = ProductionData.Cells(N, 91) '# Parts Job(3)
Case 148
M01.Cells(35, 54) = ProductionData.Cells(N, 92) 'Run Time Period(2); Job(3)
Case 149
M01.Cells(37, 18) = ProductionData.Cells(N, 93) '# Parts Job(3)
Case 150
M01.Cells(35, 55) = ProductionData.Cells(N, 94) 'Run Time Period(3); Job(3)
Case 151
M01.Cells(37, 23) = ProductionData.Cells(N, 95) '# Parts Job(3)
Case 152
M01.Cells(35, 56) = ProductionData.Cells(N, 96) 'Run Time Period(4); Job(3)
Case 153
M01.Cells(37, 28) = ProductionData.Cells(N, 97) '# Parts Job(3)


End Select
Next X


M01Map.Activate
M01.Activate
Data.Activate
ProductionData.Activate

End Sub
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I maybe wrong, I thought <b>Data</b> was a keyword that excel likes to use internally
 
Upvote 0
have you stepped through your macro with F8

Case 1 will run 256 times

Case 2 will run 255


N dosen't increment
 
Upvote 0
Sorry, but this response does not really help me out. What does the step F8 do?

All I need to know is if a macro can be run to pull data from two different worksheets simultaneously from the same active row and paste the information into two different worksheets in the same workbook.

I have tried to accomplish this through the code that I provided, but it is not working, so, I'm guessing that:

1) It is not possible to do in excel
2) Or I did not write the code correctly

Please help!
 
Upvote 0
Your For loop and Select statement aren't accomplishing anything. You can change this:

Code:
N = ActiveCell.Row
 
For X = 1 To 256
Select Case X
Case 1
M01Map.Cells(2, 14) = Data.Cells(N, 2) 'Date
Case 2
M01Map.Cells(2, 3) = Data.Cells(N, 4) 'Shift
Case 3
M01Map.Cells(19, 8) = Data.Cells(N, 5) 'Scheduler
Case 4
M01Map.Cells(21, 8) = Data.Cells(N, 6) 'Tech Support
...

to

Code:
N = ActiveCell.Row
 
M01Map.Cells(2, 14).Value = Data.Cells(N, 2).Value 'Date
M01Map.Cells(2, 3).Value = Data.Cells(N, 4).Value 'Shift
M01Map.Cells(19, 8).Value = Data.Cells(N, 5).Value 'Scheduler
M01Map.Cells(21, 8).Value = Data.Cells(N, 6).Value 'Tech Support
...
or more readably (to me)
Code:
With Data.Rows(ActiveCell.Row)    ' (where the active cell is on ProductionData)
    M01Map.Range("N2").Value = .Range("B1").Value   'Date
    M01Map.Range("C2").Value = .Range("D1").Value   'Shift
    M01Map.Range("H19").Value = .Range("E1").Value  'Scheduler
    M01Map.Range("H21").Value = .Range("F1").Value  'Tech Support
 
Upvote 0
Making these changes to the code did not help either.


Basically, what I want to do is the following

spreadsheet(1) = 'Data'

col B = Date
col C = machining cell
col D = Shift
col E = Job(1) (i.e. 12300)
col F = Job(1) Qty




spreadsheet(2) = 'ProductionData'


col B = Date
col C = machining cell
col D = shift
col E = Job(1) total parts produced
col F = Job(1) Total discrepant parts


So, I would like to write a macro that allows me to take data points from 'Data' and 'ProductionData' and copy them to two different spreadsheets in the same workbook 'M01Map' and 'M01' (one is a physical map of the machining cell and the other sheet performs calculations on the production data).

The formatting of the data corresponds one-to-one from the 'data' and 'productiondata' sheets.

Any other suggestions?
 
Upvote 0
Sorry, but this response does not really help me out. What does the step F8 do?

All I need to know is if a macro can be run to pull data from two different worksheets simultaneously from the same active row and paste the information into two different worksheets in the same workbook.

I have tried to accomplish this through the code that I provided, but it is not working, so, I'm guessing that:

1) It is not possible to do in excel
2) Or I did not write the code correctly

Please help!

The keys F8 and F5 are used to run the VBA code

F8 lets you step through each line one at a time to observe what is occuring

F8 showed me that you N value did not update, it only return the value for where the cursor was placed

and also that each case x 1 to 256 , looped through 256 times, and each time added one more case statement to undertake

all with no benefit to you actual needs
 
Upvote 0
Your For loop and Select statement aren't accomplishing anything.
Code:
With Data.Rows(ActiveCell.Row)    ' (where the active cell is on ProductionData)
    M01Map.Range("N2").Value = .Range("B1").Value   'Date
    M01Map.Range("C2").Value = .Range("D1").Value   'Shift
    M01Map.Range("H19").Value = .Range("E1").Value  'Scheduler
    M01Map.Range("H21").Value = .Range("F1").Value  'Tech Support

half way down the list the data changes focus from another page
 
Upvote 0
yes, I know that the data changes focus from a different page.

This is because I need data points from the 'Data' and 'ProductionData' worksheets to populate the 'Calculations' worksheet that I was referring to; and:

I also need the 'Data' worksheet to populate the Machining cell Map 'M01Map' That I was referring to.

I wanted to write a macro that would allow me to pull the necessary data points from both data worksheets to populate the other two forms.

Is this not possible with macro code?

Can someone help me out??
 
Upvote 0
see, i was replying to shg, just in case it had been missed, if I get time i might sit down and applying all the fixs to the macro, you have been offered so far
 
Upvote 0

Forum statistics

Threads
1,224,594
Messages
6,179,795
Members
452,943
Latest member
Newbie4296

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