Hi everyone,

I am trying to write a macro that copies data between 2 worksheets if 'sheet 2' has matching columns.
If no matching columns exist, it should tell the user so.

I know next to nothing about VB macros unfortunately but I have managed to write a macro using a FOR loop and a WHILE.
This works to a large extent. However the Cells.Find is giving me a problem.

I have also attached my workbook. When I click the macro button in the "Source" worksheet, it copies data of column A correctly (which means the Find works) but for the other columns, it cannot seem to be able to grab the whole range of cells having data. This is what I don't get and what I am trying to figure out.

If you can help me out, that would mean a lot.
Thanks!

I haven't time to look at your code today but one way to copy an entire row is
Activecell.entirerow.copy

Brian

Thanks Brian for the info.

In this case, I need to copy a column from one worksheet to the other. I will not know how long the column will be and that is a problem...
So I used a variable to store the start of the column and Cells.Find to find the end of the column --- and that's where it fails to work.

The funny thing is that it works for one column (the first) and then it just stops functioning right for all the others.

Ok took a quick look, I don't understand what the Find is doing, only used it once years ago.
It finds the row of the longest column the first time through even if that is not the longest being worked on, I took the A col out of the search, and after that returns row 4.

You have so few rows why not just copy all of them.

Normally to find the last used row in a col we use, for ColA

lastrow = Sheets("sheet1").Range("A65536").End(xlUp).Row

You would need to index the range portion, but as stated I wouldn't bother with this.

Brian

Hi Brian,

I will try out your suggestion, thanks.

The reason I used 'Find' to locate the end of a column is because I do not know how long each column would be. In the sample file I attached, the columns are short but in the actual file, they have years of data...

I got a different code to achieve the same output yesterday which I am attaching herewith. It works fine and is simpler, but we have to define a range.
The only problem with this piece of code is that it does not have error checking. If no matches are found in the Target sheet, it throws an error. Would you be able to help me with this (i.e. to add an error check)?

Thanks in advance!
Dimuthu

Ok so your columns are long so all you needed to do to get your previous code to work was replace your mylastrow = .. line with

myLastRow = Sheets(SourceSheet).Cells(65536, CellSelect.Column).End(xlUp).Row

It is also normal to use
Application.CutCopyMode = False
before leaving the sub, I notice that you leave in more than one place so place it after the Paste statement.

Brian

Thanks Brian - this weekend I will try out your suggestion.

Both my previous code and the new code throw an error if there are no matching columns in the Target sheet. If you have any advice abt that, please let me know.
Thanks again for the help!

Hi Brian,
I tried out your suggestion - it works like a charm!!! Thanks so much!! I also noticed there is no error if the Target sheet does not have matching headers.

I will upload my final version of code so that anyone browsing this page may make use of it.

Brian, if you have time to look at my 2nd code, I'm still interested in finding out how to add an error check to it (i.e. if no matching columns exist in Target, it throws an error).

Thanks so much again!!!

Took a look at the second code, as you say it is neater, also it does not fail for me ! I am on 2002 but don't think that is the reason as I can see no reason for it to give an error if there are no target values.

BTW I notice that it includes
Application.ScreenUpdating =
these statements are always worthwhile as it speeds execution, with multi exit points be careful that you catch them all.

Brian

Hi Brian,

Hmm...I don't know why it throws an error for me - but thanks for the info.

For anyone out there who may need it, I am uploading the 1st macro after modifications (the 2nd one has already been uploaded on this page).

You can remove the msgboxes which I added to help with my debugging and also add these to speed up execution as Brian suggested:
Application.ScreenUpdating = False
Application.ScreenUpdating = True

and also:
Application.CutCopyMode = False