check and Copy columns from other workbook in a loop

[kammend]

Hi,
I have a workbook into which I want to import data from a second workbook.
I am able to open the second workbook. However I am stuck at the pointy where I copy data into the first (master) workbook.

The challenge that I have is that in the master workbook I have a range of columns and that I want to check the existence of that same column in the import workbook. If that column exists, then I want to have that column copied to the master workbook. and so on...

I am able to identify the row of the header in the import workbook, however, when I try to check the existence of headers, my code fails.
I attached the master and the import workbooks as examples.

Thanks, Daniel

Excel 2013 64bits

[Marc L]

Paste this code to the Target worksheet module, update F variable for the import file full path :

Sub Demo1() 
     Dim F$, R&, V, C%, W 
         F = ThisWorkbook.Path & Application.PathSeparator & "import .xlsx" 
         If Dir(F) = "" Then Beep: Exit Sub 
    With Me.UsedRange.Rows 
         R = .Count + 1 
         V = .Item(3).Value2 
    End With 
         Application.ScreenUpdating = False 
    With GetObject(F).Worksheets(1).[A4].CurrentRegion.Rows 
        For C = 1 To UBound(V, 2) 
            W = Application.Match(V(1, C), .Item(1), 0) 
            If IsNumeric(W) Then .Item("2:" & .Count).Columns(W).Copy Cells(R, C) 
        Next 
            .Parent.Parent.Close False 
    End With 
         Application.ScreenUpdating = True 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[kammend]

Hi Marc L, I have tried the code. However I immediately get an error "invalid use of me keyword".
besides that, I have trouble understanding the code. Can you add some comment text?

Thanks

[Marc L]

You did not follow the direction just before the code as you did not paste the code to the appropriate place …

[dangelor]

Possibly...

Please Login or Register  to view this content.

[kammend]

Hi Dangelor,
Thanks. This looks very good. I love the way you make the code shorter than I did. It is almost what I need. I see that you validate the existence of a column in Target against each column in Data. Can you do the validation the other way around?
The columns in Target are the mandatory ones and each of these should be available in Data.

[dangelor]

Try this...

Please Login or Register  to view this content.

[kammend]

Yes, perfect
I managed to further fine tune the logic to how my actual workbook works.
Your help is much appreciated

[dangelor]

You're welcome.

[kammend]

Hi, after I integrated the code into my actual worksheet and did some more testing I found a strange behavior in the code....

The idea is that the header in the master sheet is found in the import sheet and if there is a match, then copy the data from the import sheet to the master sheet.
However, even when just the first part of the header finds a match the data is copied. So there is not a 100% match....

example:
header in Master: Project St
header in Import: Project Status
in this case there is not a 100% match and still data is copied....

I believe the issue is in this part of the code...

Please Login or Register  to view this content.

I uploaded the example files..

Thanks

[Marc L]

As my post #2 code yet works even with your v2 files …

[kammend]

Hi Marc L,
I'm sorry to say, but I run into the same error as in post #3 ("invalid use of Me keyword")

Please Login or Register  to view this content.

However, I do see why your code might better work.
The difference I see is the use of the FIND vs the MATCH function.

[Marc L]

Sorry to say again you didn't copy the code to the right place ‼ Just well read at least post #2 and apply directions …

[kammend]

ah... yes. My bad :-(
you are right now it does work. Thanks

[Marc L]

Yes it works … since the 19th May !

As a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped.

So thanks to click on bottom left star icon « ★ Add Reputation » of my code post !