Excel work sheets

[harry234]

Not even sure I’m asking correctly

I have 2 sheets. 1 is called “Working”, whose data is collected from the folders menu. Using the “copy path” function and pasting the results to excel “working” sheet. (This is done because the file folder is continuously updated by files downloaded from the internet.)

Sheet 2 is an inventory of those files and has line item data associated with each line item.

What I would like to do is update the inventory sheet using the working sheet but keep all inventory data associated with respective line items.

Example: Inventory sheet

	Item	Size	Location 1
1​	100 RTV Silicone Industrial
2​	880 Crown Chassis Grease (Texas Refinery Corp)	14,1oz Tube	EL2

Example: Working sheet

1​	100 RTV Silicone Industrial
2​	880 Crown Chassis Grease
3​	Ace plumbers epoxy putty

If I download a file called, Say “123 Battery”, my working sheet will look like:

1​	100 RTV Silicone Industrial
	123A Battery
2​	880 Crown Chassis Grease
3​	Ace plumbers epoxy putty

The objective would be to insert “123 Battery” in the alfa numerically correct line and keep relevant information attached to the line that was moved down. (Example Inventory Sheet below)

	Item	Size	Location 1
1​	100 RTV Silicone Industrial	8oz can	Tool
2​	123 Battery
3​	880 Crown Chassis Grease (Texas Refinery Corp)	14.1oz Tube	EL2
4​	Ace plumbers epoxy putty	20oz stick	Control

 

[jolivanes]

Not tested. Please check all references and change where required.
It assumes that both sheets are in one workbook which also has the code in it.

Sub Start_Somewhere()
Dim arrFiles, i As Long, j As Long
Dim wb1 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim sh1Arr, sh2Arr, misF
Set wb1 = ThisWorkbook
Set sh1 = wb1.Worksheets("Working")    '<----- Change as required
Set sh2 = wb1.Worksheets("Inventory")    '<----- Change as required
arrFiles = Application.GetOpenFilename("All Files (*.*), *.*", , , , True)    '<----- Choose as many files as needed.
With Sheets("Working")
    For i = LBound(arrFiles) To UBound(arrFiles)
        .Cells(.Rows.Count, 2).End(xlUp).Offset(1).Value = Left(Dir(arrFiles(i)), InStrRev(Dir(arrFiles(i)), ".", -1, vbTextCompare) - 1)
    Next i
sh1Arr = sh1.Range("B4:B" & sh1.Cells(sh1.Rows.Count, 2).End(xlUp).Row).Value
sh2Arr = sh2.Range("B3:B" & sh2.Cells(sh2.Rows.Count, 2).End(xlUp).Row).Value
    For j = LBound(sh1Arr) To UBound(sh1Arr)
        If IsError(Application.Match(sh1Arr(j, 1), sh2Arr, False)) Then misF = misF & sh1Arr(j, 1) & "|"
    Next j
misF = Split(misF, "|")
sh2.Cells(sh2.Rows.Count, 2).End(xlUp).Offset(1).Resize(UBound(misF)).Value = Application.Transpose(misF)
    With sh2.Range("A2:A" & sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row).Resize(, sh2.Cells(2, sh2.Columns.Count).End(xlToLeft).Column)
        .Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Header:=xlYes
    End With
    With sh2.Range("A3:A" & sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row)
        .Formula = "=ROW() - 2"
        .Value = .Value
    End With
End Sub

 

[jolivanes]

Of course if you don't test there will be errors.
In this part, add the "End With" as shown here.
Row references etc. are based on the pictures in your first post.

With Sheets("Working")
    For i = LBound(arrFiles) To UBound(arrFiles)
        .Cells(.Rows.Count, 2).End(xlUp).Offset(1).Value = Left(Dir(arrFiles(i)), InStrRev(Dir(arrFiles(i)), ".", -1, vbTextCompare) - 1)
    Next i
End With    '<---- Add this

There are a couple mistakes in the following section so replace these 8 lines

sh2.Cells(sh2.Rows.Count, 2).End(xlUp).Offset(1).Resize(UBound(misF)).Value = Application.Transpose(misF)
    With sh2.Range("A2:A" & sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row).Resize(, sh2.Cells(2, sh2.Columns.Count).End(xlToLeft).Column)
        .Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Header:=xlYes
    End With
    With sh2.Range("A3:A" & sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row)
        .Formula = "=ROW() - 2"
        .Value = .Value
    End With

With

sh2.Cells(sh2.Rows.Count, 2).End(xlUp).Offset(1).Resize(UBound(misF)).Value = Application.Transpose(misF)
    With sh2.Range("A2:A" & sh2.Cells(sh2.Rows.Count, 2).End(xlUp).Row).Resize(, sh2.Cells(2, sh2.Columns.Count).End(xlToLeft).Column)
        .Sort Key1:=.Cells(3, 2), Order1:=xlAscending, Header:=xlYes
    End With
    With sh2.Range("A3:A" & sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row)
        .Formula = "=ROW() - 2"
        .Value = .Value
    End With

 

[jolivanes]

More to Post #12.

In this line, change the 1 to a 2

With sh2.Range("A3:A" & sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row)    '<----- Change 1 to a 2

so it ends up to be

With sh2.Range("A3:A" & sh2.Cells(sh2.Rows.Count, 2).End(xlUp).Row)