Macro paste
Hi Guys,
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.
Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.
Is there a way to do this?
I've attached the file for you to see.
Thanks.
Nujwaan.
Last edited by nujwaan; 09-17-2009 at 10:59 AM.
data starts in N14 of he input form
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
Hey Roy,
Sorry that's what i meant.
Just insert some rows to include that title.
Can you help?
Assign this code to the button the calculator sheet
Code:Option Explicit Sub InputResults() Dim NextRw As Long With Sheets("Input Form") NextRw = .Cells(.Rows.Count, 14).End(xlUp).Row + 1 .Cells(NextRw, 14).Value = Cells(29, 4).Value .Cells(NextRw, 15).Value = Cells(24, 8).Value End With MsgBox "Done", vbInformation, "Success" End Sub
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
Hey Roy,
It didn't seem to work.
Just to clarify - what I need is the button to be click and the values from cells D29 and H24 in the Calculator to be pasted in Cells N13 and O13 respectively.
I then need to be able to change the data in the calculator to create new values and then click the button again to populate N14 and O14.
For some reason when I run the code you gave me it gives a dialog box but seems to be getting the value "ERS" and placing it in N13 with no other result.
nujwaan
As I said- the code should be assigned to a button on the calculator sheet
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
I love you Roy.
I really do.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
Hi roy,
Just a quick question on top of this.
If I want to make the code so that the button will only populate in columns N and O then how would I do this to prevent people from clicking the button and populating other columns?
The code only writes to N & OOriginally Posted by nujwaan
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
Hi Roy,
The code only seems to write to N and O if they are the cells that are selected. I was thinking it might be possible to stop people from having cell M14 selected and then clicking the macro button.
If i have M14 selected it pastes the details into N15 and O15.
Thanks.
I don't know what you mean. The code writes only to Columns N & O
Code:'Column N = 14 .Cells(NextRw, 14).Value = Cells(29, 4).Value 'Column O=`15 .Cells(NextRw, 15).Value = Cells(24, 8).Value
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
it seems njwaan is saying that even though the macro is pasting into Column N and Column O
the row it pastes into is being based on the active cell.
Ie: if M14 is selected then the macro pastes into N15 and O15 instead of the desired location of: N13 and O13 initially, when a form of loop off set so that when the macro is used repeatedly it will off set the results down the columns of the first sheet.
njwaan i hope i am expressing your concerns accurately.
The code copies the cell values to the next empty row in Columns N & O. I have tested it before posting.
The code is attached to Button 3 on the calculator sheet, captined "Copy information to Input Form"
Last edited by royUK; 09-17-2009 at 01:28 PM.
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks