I just read your post again and I realized that the columns don't match. This means we're back to the original JCR-only code.
The logic behind the macro is simple. As always, the first part (the Dims) is a declaration of every object/range/whathaveyou we will be using. There, I declared ever worksheet and range and numbers I am using.
Code:
Dim wbk As Workbook
Dim wjcr As Worksheet
Dim wcwip As Worksheet
Dim wowip As Worksheet
Dim lrow As Range
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim m as Long
Dim n as Long
After that, I set the "target locations" of each "location-based variable" (ranges, worksheets, cells go here).
Code:
Set wbk = Application.ThisWorkbook
Set wjcr = wbk.Worksheets("JCR")
Set wcwip = wbk.Worksheets("CWIP")
Set wowip = wbk.Worksheets("OWIP")
Next would be the creation of the MySheet. This is the first 'action' I'll be creating so I placed the error handling part first. This means that if the subsequent content gets an error, I will jump immediately to the part right after the errHandle: label.
Code:
On Error GoTo errHandle:
wbk.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MySheet"
Next, after creating the worksheet, I declare it as a worksheet then proceeded to set it to a variable like what I did above it.
Code:
Dim wmys As Worksheet
Set wmys = wbk.Worksheets("Mysheet")
Then, I assign numbers to the Long declarations j, l, and n. What each of the lines below does is simply go into the the respective sheet (wjcr,wcwip,wowip), start from the bottom cell of the first column (Cells(Rows.Count, 1)), then emulate pressing Ctrl-Up to get to the last row in that sheet with a value. Appending .Row to it returns the number of that row which is important in the next part.
Code:
j = wjcr.Cells(Rows.Count, 1).End(xlUp).Row
l = wcwip.Cells(Rows.Count, 1).End(xlUp).Row
n = wowip.Cells(Rows.Count, 1).End(xlUp).Row
We manipulate two things to help speed things up.
Code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Then we start with the loops. What this does is simple go through the rows one by one. If you notice, the first line is For i = 1 to j. Read in English, this is worded as, "For the value of i from 1 to the number of rows associated with j...".
The next few lines basically check for the value of column A in that row (Range("A" & i).Value <> 0) and sets the target location of the pasting. This should be inside the loop because every iteration of the copying must locate the newest next empty row. Setting it before would just result into the loop overwriting one row over and over.
Code:
For i = 1 To j
If wjcr.Range("A" & i).Value <> 0 Then
Set lrow = wmys.Cells(Rows.Count, 1).End(xlUp).Offset(1)
wjcr.Range("A" & i).EntireRow.Copy
lrow.PasteSpecial
End If
Next i
The above is read as, "From row value 1 to the maximum row number j, if JCR's respective row has a column A value not equal to 0, then locate the next empty row in MySheet and copy the row from JCR to the empty row in MySheet.". Not so confusing now is it?
The following parts are just some more loops and just closing the other parts. Error handling is basically identifying any error numbers that might come about and 'trapping' them so that the debug error never comes up but instead notifies you of what error you are encountering. This one is a debugging issue and not really something you have to worry about if everything is done correctly.
Hope this helps.
![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)