Results 1 to 9 of 9

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

This is a discussion on I need a macro to copy active cell, change sheet and paste into next blank cell in the column within the Excel Questions forums, part of the Question Forums category; Hi guys Im brand new to coding etc - I can record macros pretty well but i want to get ...

  1. #1
    New Member
    Join Date
    May 2009
    Posts
    27

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

    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

    any help would be greatly appreciated.

    regards

    Ben

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Posts
    143

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

    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

  3. #3
    New Member
    Join Date
    May 2009
    Posts
    27

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

    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

  4. #4
    New Member
    Join Date
    May 2009
    Posts
    27

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

    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??

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Posts
    143

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

    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

  6. #6
    New Member
    Join Date
    May 2009
    Posts
    27

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

    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

  7. #7
    Board Regular
    Join Date
    Nov 2008
    Posts
    143

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

    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.

  8. #8
    New Member
    Join Date
    May 2009
    Posts
    27

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

    Phil
    Many thanks mate you've allowed me to amke massive steps forward today. you have helped me out no end.

    Thanks again

    Ben

  9. #9
    Board Regular
    Join Date
    Nov 2008
    Posts
    143

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

    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!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com