Macro to copy date to specific sheet based on data from another sheet.

Good afternoon all!

I'll try to explain this as clearly as possible.

I'm trying to design a macro to copy specific information from cells on one sheet to another. However the Macro has to select which sheet to copy the data to from the data on the first sheet.

So say the first sheet has document number, name, revision, date, transmittal no. etc. The macro would copy the revision, date and transmittal no. to (Document Number Sheet)

Apologies in advance for this code, because it also functions already to create a pdf, save info in another sheet and create a hyperlink to pdf, this was just the most functional place to insert the code. I've highlighted the specific bits I'm working on in red. The main rpoblem I'm having is setting the worksheet based on a value from a cell without using a heap of preprogrammed IF statements to pick for me.

Any help would be greatly appreciated!

Please Login or Register  to view this content.

in this bit:

Please Login or Register  to view this content.

you are setting nametest to the sheet called nameease, you want it to set it to the value of the variable nameease, so you don't want quote marks:

Please Login or Register  to view this content.

alternatively:

Please Login or Register  to view this content.

With this line:

Please Login or Register  to view this content.

you haven't yet defined nametest, so you can't use that sheet reference at this point, I'm not sure what sheet you want to pick up the testrow from?

Good Morning Yudlugar, thanks for helping again. That's fine. I can define name test further down. testrow is trying to find which row to put the data into on whatever document sheet has been selected.
To explain better. This macro is taking document details from a transmittal sheet, and depending what documents are being sent out its logging the details in an individual document sheet, between rows 20-29. sometimes on a transmittal there are more than one documents, so it needs to be able to loop. Hence why I've stuck it in that loop

Please Login or Register  to view this content.

which is where the possible list of documents being sent will be.

Still seems to be having trouble selecting the sheet from data in a cell though. That's where the macro just stops.

Okay, quick update. I've managed to get it to select the sheet. it's now stopping at this line

Please Login or Register  to view this content.

I wasn't entirely sure how to code this but what I'm trying to do is. On 'Nametest sheet' fine the next empty row between rows 20-29 then copy the data to there.
Would anyone have any idea how to write this properly?

Okay, quick update. I've managed to get it to select the sheet. it's now stopping at this line

Please Login or Register  to view this content.

I wasn't entirely sure how to code this but what I'm trying to do is. On 'Nametest sheet' fine the next empty row between rows 20-29 then copy the data to there.
Would anyone have any idea how to write this properly?

Please Login or Register  to view this content.

you need to set it as a sheet

Nametest =sheets("sheet1") 'change the name of the sheet to your actual sheet
Should be the same setting as this line

Please Login or Register  to view this content.

Nametest is set as a sheet, and the reason I can't just define it like that is because the macro has to select what sheet from the data. The sheet it should copy to is named after the document number. So the macro checks the rows, whatever document is in that row in column 'h', it takes that data then plugs it into the line

Please Login or Register  to view this content.

This works now. Or at least I think it does, but it now gets stuck at the line after.

Please Login or Register  to view this content.

I know this is wrong, but I'm unsure how to write what I'm trying to do.
It should copy the data to the sheet of whatever document is being sent between rows 20-29. It should check for the next empty row between 20-29 and copy the data to specific cells.

Please Login or Register  to view this content.

Tried wording it this way, it copied the correct data to the correct columns on the correct sheet. But it pastes it in the first available row, in this case the first row, instead of pasting it in the next available row between rows 20-29.

Please Login or Register  to view this content.

has to come from a sheet.
what is Nametest? Is not that a worksheet? Why can not use that sheet name in testRow? Or use active sheet

Please Login or Register  to view this content.

Nametest is a worksheet. Nametest will be the sheet for whatever document is in question.
But select the sheet is no longer my problem, the code is working for that. I'm trying to get the data to be copied to nametest, to the next available row between B20 & N29.

Please Login or Register  to view this content.

tried doing ti this way but it just copies the data to the first row of Nametest sheet, and not within the specified range

One Step Closer. Now it's posting it to the correct place but it doesn't find the next available row, it just replaces the first row within that range.
Here's that line

Please Login or Register  to view this content.

and here's the entire code

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You have two of them. You need to choose one of them.
The second one works as long as you are pasting in to column A
For the second, you need to have a starting row, that destRow is 1 or 2 and then increases as you loop though, but the first destRow should be out side the loop, otherwise you are going back to it as you loop through.

thanks for replying AB33. Ignore the destRows, they are for a different function and that one works fine.

It's 'testRow' that I'm trying to sort out. and it's selecting the correct row, it's just not incrementing properly. Taking out the

Please Login or Register  to view this content.

just screws everything up.

Okay going to try explain whats going wrong one more time, just to encourage some answers because I imagine for someone who knows what they're doing this should be easy, I'm not that someone. Sorry If i've been explaining this terribly AB33.

I'm looking to paste data into a specific range "B20:N29", the problem is that the code currently pastes the data into row 19, and then when I try the code again, instead of incrementing and pasting in the next row, it just replaces it. Can anyone help solves this?

When you loop through r,destRow starts at 19 and increases as r does.

Please Login or Register  to view this content.

Correction, I am that someone. Even if that someone used trial and error for hours to figure it out.
Don't know why anyone else would ever need/use this overly complicated code but here's it is.

Please Login or Register  to view this content.

Now if only I could give myself rep.