Copy Paste Latest Value Macro

[Eaglboy1]

Hey there everyone. There is probably already a question about this somewhere, but I can't seem to find it, so I'm creating my own.

What I'm trying to do is this: I have a macro that uses drop down menus to populate certain fields. I'm working on creating a second macro that will run immediatly after the first that will ask a simple yes/no question. If the answer is yes, I want my new macro to only copy the latest entered information and paste it to another sheet on the first available line. All of this information will be in different locations, so I can't use standardized ranges. Is there any way to create a macro that will accomplish this?

I'd appreciate any help I can get on this. Thanks!

 

[Roderick_E]

almost all things are possible but the question is a bit vague

 

[Eaglboy1]

Lol. Sorry. Trying to be as specific as I can without giving away any proprietary information. I work for a major company that could get very upset with me for puttting even our Excel macro information out here. What's happening is I have sheet 1 which has 5 macros linked to buttons. Each button asks a list of 5 questions. Once those 5 questions are answered, it pastes the responses to sheets 2-6 respectively. I'm attempting to create a new macro that will take the information from button #4, which is pasted onto sheet #5, and have it also paste the information on sheet #2 if the yes/no is answered yes. The issue is I need it to be specific to the row of data that was just entered and not encompass the entire sheet. Does that help or is it still too vague? If need be, I can see about getting permission to paste some code out here, but that might be a pain.

 

[Roderick_E]

I've never been a big fan of pasting data since it is more difficult to determine things like the row and column where the data is going over a range. Anyhow, in your code you need to either:

1) make a variable that records where (row) the initial data was pasted and then paste it on that same row on the different sheet.
2) Do something to count the rows before and/or after the pasting so that you can duplicate this on the different sheet.

The following code determines the last populated row on the active sheet:

lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

To specify which sheet, do something like:

lastrow = sheets("name of sheet").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Do you have the YES/NO code figured out? If not, see a sample below:

yn = MsgBox("test", vbYesNoCancel, "Yes or No")
If yn = vbYes Then
'do the yes stuff here
End If
If yn = vbNo Then
'do the no stuff here
End If
'cancel do nothing

 

[Eaglboy1]

Thanks! I'll give this a shot and let you know how it goes once I've finished testing it.

 

[Eaglboy1]

Ok. I've almost got this set the way I want, but I realized that a message box doesn't quite do the trick. Instead, I've got a drop down menu that populates the Yes/No question. I'm wondering how I can link the dropdown menu to the second macro I'm using to copy paste. The code I have at the moment for the drop down menu to link it to another macro are below. (I finally got permission to post a bit of it)

Here's the code for the dropdown box

'Empty NormalMaintBox
NormalMaintBox.Clear

'Fill NormalMaintBox
With NormalMaintBox
.AddItem "Yes"
.AddItem "No"


End With


End Sub

And here's the code I was going to use for the Yes/No, but I know I've got it written wrong. I'm just hoping you can help me fix it.

Sub test()
xVal = NormalMaintBox(vbYesNo)
If xVal = vbYes Then Call Macro1

End Sub

Thanks for the help and let me know if I need to adjust this to make it work, but this is the way I'm hoping to get it to work.

 

[Roderick_E]

Hi there. Instead of what you have under the Sub test() just look at the NormalMaintBox.text and if yes then call Macro1 as you have here.
Something like

[COLOR=#333333]if NormalMaintBox.text = "Yes" then
call macro1
endif[/COLOR]

You can trigger it by a button or by the NormalMaintBox change code (right click the NormalMaintBox and view code)
Hope this gets you there

 

[Eaglboy1]

Ok. Hopefully this is the last quesiton I'll have. I'm not sure if it's the macro above that I'm having issues with or the copy paste macro, but it's deciding not to work for me. So here's the code for the Macro1 that is being called. What I want it to do is copy the information I just input and paste it to the other sheet on the next available row.

Code:
Sub Macro1()
Dim LR As Long
With Sheets("Sheet1")
lastrow = Sheets("Sheet1").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Selection.Copy
Sheets("Sheet2").Select
NextRow = Range("*").End(xlUp).Row + 1
ActiveSheet.Paste

End With

End Sub

 

[Eaglboy1]

And I did a little more experimenting and research and I realize how that code won't work, but I can't find one that will. I'm experimenting for now, but I'd still love to hear from someone. Thanks for all the help Roderick_E! Hopefully I don't sound like a complete idiot in asking all these questions.

 

[Roderick_E]

Ok. Hopefully this is the last quesiton I'll have. I'm not sure if it's the macro above that I'm having issues with or the copy paste macro, but it's deciding not to work for me. So here's the code for the Macro1 that is being called. What I want it to do is copy the information I just input and paste it to the other sheet on the next available row.

Code:
Sub Macro1()
Dim LR As Long
With Sheets("Sheet1")
lastrow = Sheets("Sheet1").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Selection.Copy
Sheets("Sheet2").Select
NextRow = Range("*").End(xlUp).Row + 1
ActiveSheet.Paste

End With

End Sub

Hmmm, what is lastrow for here? doesn't seem like it is being used for anything and the Dim LR isn't used anywhere either.
I'm not certain if Range("*").End(xlUp).Row + 1 will actually work. Test it by putting this line right after it:

msgbox NextRow

Run the macro again.

You can comment it out or delete it when done. To comment out, simply put a ' (apostrophe) in front of that line.
Ultimately, we're trying to determine where you just inputted a line, and then copy that line to the nextblank row of another sheet. I would imagine the macro to look something like this:

Sub Macro1()
Dim LR1 As Long
Dim LR2 As Long
dim sourcerng as string
dim receptorrng as string
LR1 = Sheets("Sheet1").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LR2 = Sheets("Sheet2").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
sheets("Sheet1").activate
sourcerng = LR1 & ":" & LR1
Rows(sourcerng).Select
    Selection.Copy
sheets("Sheet2").activate
LR2 = LR2 + 1 'next blank row on sheet2
receptorrng = LR2 & ":" & LR2
Rows(receptorrng).Select
sheets("Sheet2").Paste
End Sub