Finding the last used cell in the active column and paste copied data

[Megatronixs]

Hi all,

I have a master workbook where I run a maro to open a bunch of xml documents and copy data from them. Not all the workbooks have the same headings on the same place. So my macro looks for a certain name of the heading and then he copies that data only, goes back to the master workbook and looks for the same heading and paste it there. The problem I have is that I need to tell them with finalrow where to put the data. but since I use the

Please Login or Register  to view this content.

it will always check the same column, if for a reason the next column to copy is in another column, it will paste it wrong.

How can I paste in the column that is active and taking in acount that the data for the first time goes in the 3rd row and not the first (in row 1 there is the macro button and row 2 has the headings of the master workbook).

Please see my code and let me know changes I need to make:

Please Login or Register  to view this content.

Thanks in advance and greetings.

[AB33]

Try

Please Login or Register  to view this content.

will find the next empty row on entire columns.

[Megatronixs]

Hi AB33,
Thank you for your answer. How can I implement your line of code in my macro? Do I need to put the text I'm looking for in the "*" ??
I tried the code just like above, but it does not do nothing, so I have no clue how to use the code.

Greetings.
(seems my previous answer got lost)

[AB33]

I am not quite sure what the issue is. You said, you want to find the last non empty row in a data.
You have

Please Login or Register  to view this content.

which finds the last non empty row in column "A" ONLY, while my line will do the same, but it finds in all columns, not just column A.

[Megatronixs]

Hi AB33,
It should be that the new data to be pasted is in the last row that is not empty +1 row. so it goes on the empty cell without overwritting the previous data.
If I understand this, I need to change the:

Please Login or Register  to view this content.

with your line?

I will give it a try to see what it will do. (I'm still green in excel vba).

Greetings.

[Megatronixs]

Hi AB33,

I tried your code. I replaced the line with your code and it works great on the first column, when if finds the next text for the next column, it paste the data on the wrong spot. It goes and compares column 1 and then paste the new data acording to the last row +1.
Not sure where I go wrong.

Greetings.

[AB33]

Yes, but your code require almost full heart surgery, not minor one.

You are trying to match a header and copy that column with the header in to master sheet. I have done some few lines to give you some direction.

Please Login or Register  to view this content.

[Megatronixs]

Hi AB33,

Thanks for your code. Actually the first problem I did encounter was that the line "With wbk.UsedRange" failed.
I got the error: Run-time error '438": Object doesn't support this property or method

In my previous code the FinalRow did remember on which line it was last, maybe just reseting the finalrow will do the trick.
Do you have any idea how I could reset it so it will take the new row number?

Greetings.

[AB33]

I have amended it. Try it again.

[AB33]

Or better, could you please attach a sample of both destination and source sheets.

[Megatronixs]

Hi AB33,

Sorry, but I can't attach workbook now.

I can give you some details about the look of the workbook.

The master workbook is like:

Row1 Macro button

Row2: /Tester1 in cell A2, /Tester2 in cell B2 and so on

(the rest of the rows below row 2 are empty)


One XML file in the same folder where the macro workbook is:

Row 1 has the headers, /Tester1 in cell A1, /Tester2 in cell B2 and so on

Row 2 starts with the data. (some times the next XML file in the folder has 1 to 2 columns less information. The amounts of rows depends from a few to a few hundreds.


So the macros needs to find the same name of the header and copy that range, than paste it in the next empty row in the same column that the header is with the same name. Some times when there is an extra column, there is only information in 1 row.
If the columns would be always the same, then it would be much easier to make the macro, but since I have the columns on different places, it needs to have the finalrow shifting into the active column.

Sorry for my green knowledge of vba and no clue how to ask what I need.

Greetings.

[Megatronixs]

Hi AB33,

Any solutions possible ;-)

Greetings.

[AB33]

I am not sure if you can understand the attached code and should be able to amend it. I wrote it for similar thread, but there are few tweaks need to be done.

Please Login or Register  to view this content.

These two lines are from the source sheet. The first line refers to a row and the second line to each cell in row 1.
So, the code match each cell with the headings on the master sheet.
If you are looking for a single word, you do not need these two lines. Instead, you substitute sfind by the search word.

Please Login or Register  to view this content.

You also need to change this path which is mine in to your own folder.

Please Login or Register  to view this content.

[Megatronixs]

Hi AB33,

As I have Excel 2003 and are not able to open it, any chance this can be exported so I can open it?

Thanks a lot and greetings.

[AB33]

Sorry!
I did not notice your profile.

[Megatronixs]

HI AB33,

I did give it a try, but to difficult for me to get things goin.
I managed today to make the previous macro work for 100%. The only thing I miss is that if the macro looks for a word in the column and it does not find it, then it should not stop the code but continue (I think it is error traping).
Let's say one workbook does not have the column /Tester3, then it should skip it and go looking for /Tester4.

the part that this would be handy are those columns, but the tricky part is that this one has only one cell with data in the column. So it would copy that cell, go to the master workbook and past it in the correct possition. when it makes again a pass to the same column and will paste it in the cell that coresponds to the same row with the new data. But still I guess this will be really complicated to make it work.

Greetings.

[AB33]

Could you please post the code which does not work 100%.

[Megatronixs]

Hi AB33,

Sorry for not answering ealier, I have just to many things going on and I can't work on this. As soon as I have more time, I will come back to it.

Greetings.