Extracting and transferring data across sheets

[ArtVandelay]

Hi,
I'm trying to write the code to get certain rows of data on one sheet to transfer to another sheet. The data sheet is denoted "Sheet1" and the compiled sheet is denoted "Sheet2". The compiled sheet has one row of input that includes values as well as 8 categories/classifications. If all of those 8 categories match an entry in the data sheet, then the entire row will be transferred to the compiled sheet. All of the data fields match up in each sheet; that is, the qualifications are in columns 19-26 in both. The rows to be transferred will include columns 2-26, and will go into the second sheet starting at row 3.
I'm using nested looping structures to do this, and what I have seems as if it'll make sense, but when I go to run it, there is a type mismatch. The part that gets highlighted is at the very beginning: Private Sub Transfer()
What am I doing wrong?

-------------------------------------------------------

Please Login or Register  to view this content.

[arlu1201]

I have not checked if your code does what its supposed to, but i have corrected it so it shouldnt give you any errors. Try this

Please Login or Register  to view this content.

[ArtVandelay]

Thank so much! But when I tried it it said "subscript out of range", but didn't give any indication where the code is faulty.. ?

[arlu1201]

Are you still getting the error? Check if your sheets are named as "Sheet1" and "Sheet2" as per the code.

[ArtVandelay]

I checked the names, there was one error, so now it doesn't display the error message. But nothing happens when I run the macro

[arlu1201]

Can you upload the file you are using with the macro? Remove any sensitive data.

[ArtVandelay]

Here, I made a dummy file using all the same code/formatting as the one that I'm working on. Thank you for your help!!
Dummy Program.xlsm

[arlu1201]

I click on Enter data button on sheet2 and got the form. I filled it up and the data came into Sheet2.

Now i didnt see any button called "Transfer" to move the data to Sheet1. How is that code triggered?

Also, you need to place that code in a module and not in the "ThisWorkbook" section.

[ArtVandelay]

I hadn't set up an ActiveX control button yet, I had just opened it in Visual Basic and used the run button, which had worked when I was developing the other macros.
And okay, thanks, I didn't know that had to be a module, I'll change that.
Also, there should only be one line of input data on Sheet2 (very specific work project), so those qualities of one row are the only ones to be checked against the sheet of other data.

[arlu1201]

Ok..did you try running the macro after putting it in the module? Did you get any error? If you got an error, when you clicked on debug, which line of code was highlighted?

[ArtVandelay]

The part that was highlighted was the part where I was trying to find the last row:
Lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)

[arlu1201]

Change it to

Please Login or Register  to view this content.

[ArtVandelay]

It still highlights as "subscript out of range" :/ Thank you though

[arlu1201]

Is your sheet named "Sheet1"?

If yes and it still does not work try this

Please Login or Register  to view this content.

[ArtVandelay]

Yes it is named Sheet1... same error with that code too

[arlu1201]

Can you attach the file that you are using? Remove the sensitive data.

[ArtVandelay]

Here's what I have.. I've tried lots of different things but this is all I have that's solid.
There is already input in sheet2 so there's no need to have to populate it with the form. Also I haven't programmed the Transfer button to open the macro yet; I was still doing everything in the VBA editor.
Comparison Program.xlsm

[ArtVandelay]

Bump?? Still haven't figured this out

[arlu1201]

I made some more corrections to your code. Run it and see if it gives you what you need.

Please Login or Register  to view this content.

The Private Sub CommandButton3_Click() you see is for the Transfer button. Its named as CommandButton3 in the backend.

[ArtVandelay]

Wow thanks so much!!!
It does transfer the data, but doesn't return in the right format (nothing is on the same row), so I'll just tweak it a little bit.

[arlu1201]

Yeah i noticed that but didnt know in what format you want the output, so i didnt change anything.