show values from one workbook in another workbook

[AnthonyMinnaar]

Hi folks,

I was wondering if there's a way to get values (with a filter or find function) to show up in a workbook, by looking the values up from another workbook.

the left photo (workbook 1) contains all the values and workbook 2 needs to show the values with offsets to the name and article number.
There's different spacing between "regel", but the offsets to the orange values are always the same.

Can someone help me out writing a code for this, as I'm quite new to Visual Basic?

Thanks in advance, and I'm looking forward to working this one out.


have a good weekend,

Anthony.

 

[mumps]

Place this macro in a regular module in Workbook1. Change the sheet names (in red) and workbook name (in blue) to suit your needs. Make sure that Workbook2 is also open.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim myRng As Range, desWS As Worksheet
    Set desWS = Workbooks("Workbook2.xlsx").Sheets("Sheet1")
    For Each myRng In Sheets("Sheet1").Range("B:B").SpecialCells(xlCellTypeConstants).Areas
        myRng.Offset(1, 2).Resize(2).Copy
        With desWS
            .Range("B" & .Rows.Count).End(xlUp)(2) = myRng.Cells(1)
            .Range("C" & .Rows.Count).End(xlUp)(2).PasteSpecial Transpose:=True
        End With
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = False
End Sub

 

[AnthonyMinnaar]

Place this macro in a regular module in Workbook1. Change the sheet names (in red) and workbook name (in blue) to suit your needs. Make sure that Workbook2 is also open.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim myRng As Range, desWS As Worksheet
    Set desWS = Workbooks("Workbook2.xlsx").Sheets("Sheet1")
    For Each myRng In Sheets("Sheet1").Range("B:B").SpecialCells(xlCellTypeConstants).Areas
        myRng.Offset(1, 2).Resize(2).Copy
        With desWS
            .Range("B" & .Rows.Count).End(xlUp)(2) = myRng.Cells(1)
            .Range("C" & .Rows.Count).End(xlUp)(2).PasteSpecial Transpose:=True
        End With
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = False
End Sub

Hi Mumps,

thanks for helping. this works!

 

[AnthonyMinnaar]

Hi again,

Now I am trying to implement this into the real sheet, but I can't get it to work - I'm not sure how to set the offsets (see picture). Is itpossible to have an offset on merged cells?

the red marked circle is what it is searching for and the green marked cells is the information it needs to show.


with kind regards,

Anthony.

 

[mumps]

A macro that works on a sample file most often will not work on your actual file. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your actual sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[AnthonyMinnaar]

Hi Mumps,

I've made your first code work. Works like a charm now!

Thanks alot, again!

 

[mumps]

You are very welcome.