i'm trying to write a macro to copy a row from sheet 1 to sheet 2, but the row can be varied by the user. in other words they can select row 15 or row 35 or any other. i can do the copy and paste bit but cant figure out how to get the macro to copy only the selected row. can anyone help please
Something as simple as this might work for you:
This will take your selection and paste it into Sheet2 starting in cell A4. Change your destination as needed.
Please Login or Register to view this content.
You can attach this code to a command button, or just run it right from the Macros window, ALT+F8.
this wont work as the users have no knowledge or interest in changing the macro, so basically they wont change the "A4" i really need a way that they can just highlight the row they want to copy and the macro does the rest for them
Well they'll either need to get an interest in changing something quickly, or there's no way this can be done. Is Excel supposed to psychically guess where the user wants the data from Sheet1 pasted into Sheet2?
If it's always the same place, we can code it into the macro. If the destination differs, we can have the macro pop up an input box asking the user which cell to place the copied data.
For example, if they want to copy Sheet1 A5:E5, do they want that pasted into Sheet2 A5:E5? Or in a dedicated location?
only the row to be copied from sheet 1 would vary, the destination row in sheet 2 would always remain constant
In the line: Selection.Copy Sheets("Sheet2").Range("A4")
The 'Selection.Copy' part will copy whatever the user has selected on Sheet1.
The 'Sheets("Sheet2").Range("A4")' part is the destination. Therefore, just change the destination from A4 to something else, and you'll be all set.
i was half asleep when i read the first reply and didnt understand this has solved the problem for me and i can now finish the job thanks a lot mate appreciate your help and patience
No problem. I'm lucky enough to usually be at my best when I'm half asleep. It's those other pesky 20 or so hours per day that get on my nerves.
Glad it's sorted out now.
Just a quick note,
This code will copy the active row that the user is on,
so the user will not have to select anything
Please Login or Register to view this content.
or this to paste to the first blank cell after the lst used cell in sheet 2 column a
Please Login or Register to view this content.
Last edited by davesexcel; 04-21-2007 at 10:24 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks