Import columns based on row headers and matching

[Daktari]

First off; I'm sorry I dont have any example code since I'm unsure to how tackle this issue.
As per today I have a nice little code which imports data from a mastersheet to my input sheet. Though it will only work as long as the mastersheet is static.

I'm trying to match the product code in column A between my mastersheet and my workbook with a input sheet. And then copy the cell value in column D and E of the relevant row.
That's issue ONE.
The messy part is that some of the products are split into sub categories (less than :$$$:, between :$$$: etc) and they dont have any info in column A.
That's issue TWO.

I'm unsure if this is doable. Is it? And if I get permission to edit the mastersheet somewhat re: issue TWO, could a kind soul post some sample code for me to attack issue ONE with?


I tossed in the start of my currect static c/p, thought I don't think I'm keeping it if I get a handle on this match macro issue.

Dim ws As Worksheet, wb As Workbook, t As Date, wb1 As Workbook, wb2 As Workbook, wbName As String, janei As String, spm As String
Dim fil
Set wb1 = ActiveWorkbook
ChDir ""
fil = Application.GetOpenFilename
''
If fil = False Then 
MsgBox "", vbExclamation, "."
Exit Sub
Else
spm = "?"
    janei = MsgBox(spm, vbYesNo, "")
    If janei = vbNo Then
         Exit Sub
    Else
    End If
End If
Application.StatusBar = "" 
t = Now()    
    Set wb2 = Workbooks.Open(fil)
    wbName = ActiveWorkbook.Name
   
    With wb2
        .Sheets("important figures").Range("d14:e753").Copy
    End With
    With wb1
        .Sheets("input").Range("d29").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
...

 

[Warship]

I don't understand your needs.
Post before & after sheet examples.

 

[Daktari]

I have a mastersheet that is dynamic in regards to rows. I have my workbook where the import sheet is static, since I use cells there on other sheets for calculations.
When there is a revised edition of the mastersheet I need to update cells on my sheet, but I have to make sure I get the cell (price) for the correct row (product). I'm trying to do that by having a ID column in both the master and my workbook to ensure a row match. Then I grab the values from 3 columns in the master row and paste them into my sheet, having products with updated price values.



On this code I'm trying to get the values from the E, F, G columns based on the ID column A on the master,
and paste it into column D, E, F based on the ID column in J on my sheet.
I _think_ this code fails because there are rows in my input sheet beneeth which i cant move, so that is why my ID column is in J (there was previously nothing there).

Mastersheet relevant rows: 14 through 753. The are currently mirrored on input at row 29 through 768.

-Warship, does this make any sense?

Dim wsSource As Worksheet, wsDest As Worksheet
Dim wbDest As Workbook
Dim lBRSrc As Long, lBRDest As Long, lNum As Long
Dim fisOpen As Boolean
Dim rFound As Range
Dim Result As Variant

   destfile = Application.GetOpenFilename
         Set wsSource = Application.ActiveWorkbook.Sheets("input")
    With wsSource
'because i read the ID from column J
          lBRSrc = .Cells(.Rows.Count, 10).End(xlUp).Row
    End With
'on input the rows start at row 29
         If lBRSrc > 28 Then
                 fisOpen = WorkbookIsOpen(destfile)
        If Not fisOpen Then
            Set wbDest = Application.Workbooks.Open(Filename:=destfile, IgnoreReadOnlyRecommended:=True, _
            Password:=vbNullString, UpdateLinks:=False)
        Else
            Set wbDest = Workbooks(destfile)
        End If
         
        Set wsDest = wbDest.Sheets("mastervaluesB")
        With wsDest
             
            lBRDest = .Cells(.Rows.Count, 1).End(xlUp).Row
            For lNum = 1 To lBRSrc
'match row IDs, A in master and J in input                 
                Set rFound = .Range(.Cells(2, 1), .Cells(lBRDest, 1)).Find(What:=wsSource.Cells(lNum, 10), _
                LookIn:=xlValues, LookAt:=xlWhole, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
                If Not rFound Is Nothing Then
'c/p the relevant cells for updating                     
                    Result = rFound.Offset(ColumnOffset:=4).Value
                    wsSource.Cells(lNum, -6) = Result
                    Result = rFound.Offset(ColumnOffset:=5).Value
                    wsSource.Cells(lNum, -5) = Result
                    Result = rFound.Offset(ColumnOffset:=6).Value
                    wsSource.Cells(lNum, -4) = Result
                End If
            Next
        End With
    Else
        MsgBox
    End if