+ Reply to Thread
Results 1 to 22 of 22

Copy the active cell and paste into another sheet

  1. #1
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Copy the active cell and paste into another sheet

    Hi - I have a macro that creates new sheets and it works fine except one of the criteria is based on the contents of the active cell of a sheet.
    I've tried using ActiveCell.Copy, but that doesn't work.
    Is there a VBA script that will copy the contents of the cell then follow my macro to create the new sheet when it pastes?
    I have to highlight the numbers in the cell, copy, then run the macro so it pastes from the clipboard - this if frustrating and not making the process automated.
    Any suggestions?
    Last edited by Christopherdj; 03-19-2015 at 07:02 PM.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy the active cell and paste into another sheet

    Does the active cell have a range address? Avoid using active cell, all cells have range addresses.

  3. #3
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Copy the active cell and paste into another sheet

    The cell address changes each time, always in Column A, but moves down one each time. I would like to click in the cell to be copied, then active the macro to copy that data, then run with the remainder of the script.
    The copy function needs to be at the start. Part of (the start) of the vba is as follows:

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy the active cell and paste into another sheet

    What is next in ActiveSheet.Next.Select? Is a code name?


    Please Login or Register  to view this content.
    very bad code as it will fail at some point. The active cell depends on where the cursor happen to be.
    In which column/s are the values to be used for naming the sheet? Column B?

    OR you could use selection with multiple cells

    Please Login or Register  to view this content.
    Last edited by AB33; 03-11-2015 at 06:48 PM.

  5. #5
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Copy the active cell and paste into another sheet

    The idea is it copies a number (client ID) from a selected cell, and yes it must be the one selected prior to running the macro. The macro creates a new sheet (and only one sheet which is all it needs to do), then renames the sheet based on the contents of another cell, in each case at B1. Each time I add a new client, I select the cell with the new client id reference, and I want to macro to copy the contents of that cell, then paste it into the new sheet at R1C4.
    Here is the full code.
    Please Login or Register  to view this content.
    Can you add to it so it will copy the current cell, then progress to the rest of the macro?

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy the active cell and paste into another sheet

    It would be easy to see your request in a sample. Please attach a workbook sample with desired result.

  7. #7
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Copy the active cell and paste into another sheet

    See the attached. The only way I can get a sheet to be created is to swipe and copy the new ID, exit from the cell, then run the macro.
    It doesnt need to loop, as I only create one client at a time. In this instance, the second ID in the Summary sheet in Column A needs to be used to create the new sheet.
    The new sheet is based on using the ID number.
    Attached Files Attached Files
    Last edited by Christopherdj; 03-12-2015 at 06:34 PM.

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy the active cell and paste into another sheet

    Chris,
    I have tried to adjust the code, but will prone to errors. For e.g.
    ActiveSheet.Next.Select- This line comes from the sheet black, but if the cursor is on this sheet, you get the error, so the cursor has to be on a different sheet before the code.
    The reason for keep getting error was the first copy was cancelled by the second copying of the sheet, so when the code reaches the paste section, it has not got range to copy and hence for the error.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy the active cell and paste into another sheet

    See if this does what you need?
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Copy the active cell and paste into another sheet

    AB33:
    It doesn't work properly, and also creates a "Surname" Sheet.

    John H. Davis:
    It isn't what I expected in terms of the process as you have it creating the last ID in the list.
    What you've created is on the right track, but it would be better if it only copied the cell where I will place the cursor each time, instead of looking for the last entry in the summary sheet.

    As I am not the only one setting these up, it may be that many clients are added to the list, but won't have sheets created immediately, so I am looking for selecting the cell with the client's ID to be created. So in reality, I may have 10 entries in the summary list, but only wish to create a sheet for the fourth person, or second, or eighth, which is where the cell (for the ID) selection comes in.

    Any suggestions?


    p.s. I also understand the issue you have raised with the copy/paste instruction, it makes sense as to why my solution would not work.
    Last edited by Christopherdj; 03-15-2015 at 10:10 PM.

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy the active cell and paste into another sheet

    Chris,
    I have got the name from this line


    Please Login or Register  to view this content.
    The new sheet is based on using the ID number

  12. #12
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Copy the active cell and paste into another sheet

    HI to both of you. Thanks so far but the solution is not quite right.

    Your solution is placing hard data, whereas my template and original code has a vlookup formulas.
    The data in Cell B1 of the template file is a vlookup, so when your script creates a new sheet then renames the sheet, it needs to leave all the vlookup codes.

    This naming of the sheet uses vlookup using the data from Column M of the Summary Page.
    I have variations on what is in the vlookup, and is not always just a student name from columns B and C, that is why all of the vlookup codes needs to stay.
    Your solution of placing values instead of vlookup does not allow for a single change of ID number or DOB if originally entered in error, with the summary page and vlookups, it allows for a once only change that flows into the individual sheets.
    Last edited by Christopherdj; 03-16-2015 at 08:23 PM.

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy the active cell and paste into another sheet

    Try this in the summary module:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  14. #14
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Copy the active cell and paste into another sheet

    Thanks xladept, not sure of what the summary module is??
    I have the following in as a VBA Macro:
    Please Login or Register  to view this content.
    Or are you saying to paste into the Microsoft Excel Objets, selecting the summary page, then paste into that one?
    BTW - thanks for looking at it for me.

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy the active cell and paste into another sheet

    Yeah - paste into that one and you're welcome!

  16. #16
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Copy the active cell and paste into another sheet

    OK, did that. I like what you've done but as it is built into the sheet I'm finding it runs when I go to the summary Page.
    Can we convert it to a macro - others have to use this and it needs to be "dummed down" a little as they won't be able to fix it when the debug error occurs.

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy the active cell and paste into another sheet

    I'll take a look tomorrow!

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy the active cell and paste into another sheet

    Try this in the "ThisWorkbook" module:

    Please Login or Register  to view this content.
    *If you double-click on a "name" sheet it will bring you back to the summary sheet

    And this in a Standard Module:

    Please Login or Register  to view this content.
    Last edited by xladept; 03-19-2015 at 04:23 PM.

  19. #19
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Copy the active cell and paste into another sheet

    Thanks XLADEPT - excuse my ignorance, but is it possible for you to upload the sheet (with the above added) for me to download?
    I can then try to understand what it is, and how it does works. I'm not really understanding any of this, my sincere apologies, your knowledge is so much greater than mine.

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy the active cell and paste into another sheet

    Here you are:Excel Forum Roll.xlsm

  21. #21
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Copy the active cell and paste into another sheet

    Thanks so much - another quick response and resolution. Brilliance as always.

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy the active cell and paste into another sheet

    You're welcome and thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Copy from one sheet and paste two cells to the right from active cell on another sheet
    By pasqualebaldi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-10-2013, 01:16 PM
  2. Copy Cell Range From Previous Sheet and Paste to Active Sheet
    By jtal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-01-2013, 03:26 PM
  3. [SOLVED] Copy & Paste active row to Sheet 2 without cell borders
    By XxCMoneyxX in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-12-2013, 11:10 AM
  4. [SOLVED] Active Cell Copy And Paste Sheet to Sheet
    By A.R.J Allan Jefferys in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-03-2006, 09:10 PM
  5. Copy from active sheet and paste into new sheet using info from cell in active
    By Ingve in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2006, 06:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1