I need a macro to copy active cell, change sheet and paste into next blank cell in the column

BenWard7

New Member
Joined
May 27, 2009
Messages
27
Hi guys
Im brand new to coding etc - I can record macros pretty well but i want to get into this side of things
Im currently trying to create a macro that will copy 2 hidden (hidden due to having concantenation formula the user doesnt need to see) cells on a specified row (is there anyway to create a window where the user can specify the row the info will come from) (columns N & O) and then move to another sheet ("Round up") to paste the 2 cells in the next blanks in Columns A & B

im really struggleing with the selecting of the row required and the moving down the column to paste in the next blank sheet:confused:

any help would be greatly appreciated.

regards

Ben
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hey mate,

To give the user the opportunity to enter the row number, you'll want to use an input box.

dimension a variable as Long, and then assign that variable to the input box results, something like this...

Code:
Dim lngUserRow As Long
 
lngUserRow = InputBox("Please enter the row number", "User Input Required")

Alternatively, if you don't really want the user to enter the row, and instead just scroll down through all rows, you can build this variable into a loop, incrementing it by one each time.

To select the next blank cell in your destination sheet, i always use the following method (assuming a column of A)...

Code:
Sheets("Round Up").select
Range("A65536").End(xlup).offset(1,0).select

What this basically does is takes the very last cell in col A (in excel 2003 and earlier), go up to the next cell above with data in, then moves down one row (and selects).

Give those a shot - if you need more detailed help (i.e. to write a sample code for you) then let me know.

HTH
 
Upvote 0
Phil,
Many thanks i shall try and give these a go now see how I get on.

i shall come back and let you know how i got on
 
Upvote 0
just a quicky, how would i get the number selected in the input box to go into the next bit of code for copying the Data in N & O??
 
Upvote 0
in the example i gave above, you're assigning the User's row number to a variable, so you pass that variable into the next bit of code.

e.g.

Code:
range("N" & lngUserRow & ":O" & lngUserRow).Copy

That should work...

HTH
 
Upvote 0
Hi This is the code i input
Sub ArchiveUpdate()
'
' ArchiveUpdate Macro
' Macro recorded 28/05/2009 by 87332112 - Ben Ward
'
'
Dim lngUserRow As Long

lngUserRow = InputBox("Please enter the row number", "User Input Required")
Range("N" & lngUserRow & ":O" & lngUserRow).Copy
Sheets("Round up").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

End Sub

When assigned the marco I got the following message

Compile error:
invaild outside proceedure

this pops up in a small window and opens VB with this section of the code highlighted (bold)
Dim lngUserRow As Long

lngUserRow = InputBox("Please enter the row number", "User Input Required")
Range("N" & lngUserRow & ":O" & lngUserRow).Copy

nots sure what to do now
 
Upvote 0
Odd, i just copied that code exactly as you wrote it, and it worked fine for me?

Invalid outside procedure normally means you've got some code outside of the sub.....end sub code.

One tip, i'd probably just make the following tweak...

Code:
Sub ArchiveUpdate()
'
' ArchiveUpdate Macro
' Macro recorded 28/05/2009 by 87332112 - Ben Ward

Dim lngUserRow As Long

lngUserRow = InputBox("Please enter the row number", "User Input Required")
Range("N" & lngUserRow & ":O" & lngUserRow).Copy
Sheets("Round up").Select
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub

The PasteSpecial will only paste the values you have copied, which will likely make your results look neater (unless you specifically want the formatting copied over too). and the CutCopyMode = False will clear the clip board.

You may also want to set the code back to your first sheet at the end.
 
Upvote 0
Phil
Many thanks mate you've allowed me to amke massive steps forward today. you have helped me out no end.

Thanks again

Ben
 
Upvote 0
No worries mate, glad I could help.
I'm self taught, so i appreciate someone giving it a try themselves. Everyone needs a helping hand sometimes (i've got questions outstanding on here!).
Good luck dude!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top