Macro Help Needed

[ticaroheath]

Good Day Everyone!

I am in need of some help creating a macro for a project for work. I am a production scheduler. Here are the details. (sidenote: I sometimes suck at explaining so please question anything that doesn't sound right)

I have 4 tabs: Sheet 1, Sheet 2, Sheet 3, and Sheet 4.

I have information on Sheets 1,2,&3 that all have different information about the same ID number. I pull from several screens that all have different information about the same ID number.

Is there a way to take information from Sheet 1,2,&3 and combine it on Sheet 4? BUT I need it pulled by date (so i can schedule it properly) and the correct information goes to the proper column.
I assume that it would pull by date, several rows would have the same ID number and the info would fill into the columns as assigned. I'm not even sure if that's possible, but if its not, I'm super open to any great ideas!

Any help on this would be greatly appreciated.

 

[Saurabhj]

Hi, please check below code for the first goal.

The code will copy the data from all the 3 sheets and paste the data according to column in Sheet4.

• The column heading in Sheet4 must match with other sheets column to copy the data in corresponding column.

Sub copyData()
    Dim targetRow As Integer, sourceRow As Integer
    Dim targetCol As Integer, sourceCol As Integer
    Dim ws As Worksheet, sheetnum As Integer
    Application.ScreenUpdating = False
    Worksheets("Sheet4").Activate
    targetCol = Worksheets("Sheet4").Cells(1, Columns.Count).End(xlToLeft).Column
    For sheetnum = 1 To ThisWorkbook.Sheets.Count
        sourceRow = Worksheets(sheetnum).Cells(Rows.Count, 1).End(xlUp).Row
        sourceCol = Worksheets(sheetnum).Cells(1, Columns.Count).End(xlToLeft).Column
        
        Set ws = Worksheets(sheetnum)
        If ws.Name <> ActiveSheet.Name Then
            targetRow = Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1
            ws.Range("A2:A" & sourceRow).Copy Worksheets("Sheet4").Range("A" & targetRow)
            
            Worksheets("Sheet4").Range("B" & targetRow).FormulaR1C1 = _
                "=IFNA(INDEX(" & ws.Name & "!R1C1:R" & sourceRow & "C" & sourceCol & ",MATCH(Sheet4!RC1," & ws.Name & "!C1,0),MATCH(Sheet4!R1C," & ws.Name & "!R1,0)),"""")"
            Range("B" & targetRow).AutoFill Destination:=Range("B" & targetRow & ":B" & targetRow + sourceRow - 1)
            Range("B" & targetRow & ":B" & targetRow + sourceRow - 1).AutoFill Destination:=Range("B" & targetRow & ":CM" & targetRow + sourceRow - 1), Type:=xlFillDefault
        End If
    Next
    Worksheets("Sheet4").Cells(1, targetCol).CurrentRegion.Copy
    Worksheets("Sheet4").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    MsgBox "Data copied successfully"
End Sub

 

[ticaroheath]

Thank you!! this worked perfectly.

Quick hypothetical question. Is there a way to match information from one column to another?
Example. If I have notes Column B, and a part of that note contains a number that is in Column A, is it possible to link those two together somehow? Or have it triggered to fall in the row beneath it?

 

[Saurabhj]

Great. Thanks for the feedback.

yes, we can compare the columns. If you share an example, I will surely check for the solution.

 

[ticaroheath]

My thoughts are, I have notes in Column M: Specifically the ID number. Pasted below.

That ID number is somewhere in Column A, with its own row of information.

Is it possible to link that somehow? or maybe create a list on another sheet where (using the example above). Where I see my ID 142410 in column A, my column M has an ID number in it, based on column J showing "Not enough Inventory". So is there some way i can link the ID (if there is one) in column M with where its located in Column A.

Any ideas are open. I just want to be able to have direct visibility of it somehow.

 

[Saurabhj]

Hi,

Please check if below formula fulfils the requirement.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	Prod Order	Item Number	Due Date	Parent Item	Desc	Work Center	Component Item	Dept	Loc Avail	Comp Status	Receipt Source	Recipt Ref	Link
2	142410									Not Enough Inventory			Prod Order: 01230100 ID: 142609 ST:R
3	103495									Not Enough Inventory			Prod Order: 07200216 ID: 83616 ST:R	yes it is
4	83616									yes it is
5
6
Sheet1

Cell Formulas
Range	Formula
N2:N3	N2	=IFNA(INDEX($J:$J,MATCH(VALUE(TRIM(MID(M2,FIND("ID:",M2)+3,FIND("ST",M2)-FIND("ID:",M2)-3))),$A:$A,0)),"")

 

[ticaroheath]

This doesn't seem to be working for me. I am not receiving any type of link in column N when inputting the formula.

 

[Saurabhj]

Please share your data and the formula you applied. Will help us to check and share solution.

 

[ticaroheath]

I created the column N "link" and appiled the formula to cell N2

 

[ticaroheath]

Upon further review, it looks like the formula is pulling the status in column J for the ID in column N. That's not exactly what I need. I would like for column N to show the information in column M about the ID in Column M. Does that makes sense?

Column M may have "ID: #####" That ID will be in column A and have information in Column N also. I would need that information in Column N in the cell next to "ID: #####"

Please let me know if I need to clarify it better

 

[ticaroheath]

Any luck on this?