+ Reply to Thread
Results 1 to 7 of 7

Saving cells to another worksheet

  1. #1
    Registered User
    Join Date
    10-18-2011
    Location
    Knebworth, England
    MS-Off Ver
    Excel 2007
    Posts
    18

    Saving cells to another worksheet

    Hi,

    I have a main sheet where i would fill in cells with certain information into cells that are arranged in different positions on the sheet, ie not in uniformed rows or columns.

    I have these values copied to anther worksheet to form a new record into an exisiting dynamic range.

    To do this I use a macro that copies each individual cell (there are 23) and places them into the correct columns of a given row. This works fine. However, when I run this the macro navigates between the worksheets 23 times which is not easy on the eye. Is there a way to get the macro to do the process without jumping back and forth?

    A second issue is how do I get the macro to find the last row to then start adding the cells along the new row? Currently, I begin the macro by inserting a new row at the top just below the headers row and then just fill in the cells from left to right into specific cell locations along row B. Once complete, I get it to do an A-Z sort on the first column at the end of the macro run.

    any ideas please?

    Cheers Rob.
    Last edited by RobM_01; 10-19-2011 at 07:58 AM. Reason: Solved by JBeaucaire

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Saving cells to another worksheet

    1) Post your code (in between code tags, as per forum rules) so we can spot directly the best ways to eliminate screen flicker

    2) Use Application.ScreenUpdating=False in your macro before the heavy lifting starts, set it back to =True at the end. By not writing to the screen during processing, things get way faster.

    3) Eliminate bad habits like Select, Selection, ActiveCell, Activate everywhere possible. Send your commands directly to the sheets/ranges without selecting.
    Please Login or Register  to view this content.
    ...improves to:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-18-2011
    Location
    Knebworth, England
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Saving cells to another worksheet

    Hi JB,

    thanks for posting your suggestions, much appeciated.

    I added the Application.ScreenUpdating = False / True into the macro and that sorted out the flickering issues.

    As you can see I will need to re-write all the code to make it more efficient as per your next suggestion.

    I'll give that a go, BTW how would I get it to find the last row and take the next available row to write into?

    cheers Rob.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Saving cells to another worksheet

    I don't have xl2007 so I can't test the changes in the .Sort section, so go over that... this should get you 99% of the way there:
    Please Login or Register  to view this content.


    The use of variables to store your worksheets means you can change your worksheet names in on place at the top of your macro, and all the commands later update to use the new sheet names, far less "editing" when you make big changes. Plus now we can send our commands directly to the sheets/cells without any selecting.

  5. #5
    Registered User
    Join Date
    10-18-2011
    Location
    Knebworth, England
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Saving cells to another worksheet

    Thats works spot on!!

    can I also send the number formatting? or at least make sure that the cell formatting is included for the new row so when the details come in its all correct in terms of the format. At the moment if it copies across 100% but it fills the cell as 1.

    The source format is a % cell but the destination is set to general. The dynamicrange has the formatting to each column but when a new value is entered it seems to revert back to general for that new row.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Saving cells to another worksheet

    We're transferring values, one would presume the format of those source cells does not change.

    So, format the COLUMNS on your MixList sheet to your liking and you're done, no more need to twiddle with them. New rows will use the inherent formatting you've applied.

  7. #7
    Registered User
    Join Date
    10-18-2011
    Location
    Knebworth, England
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Saving cells to another worksheet

    Quote Originally Posted by JBeaucaire View Post
    We're transferring values, one would presume the format of those source cells does not change.

    So, format the COLUMNS on your MixList sheet to your liking and you're done, no more need to twiddle with them. New rows will use the inherent formatting you've applied.
    Ah yes, appologies my mistake, my sample MixList sheet i only formated the existing range. I did the columns as you say and all is great now.

    thanks for all your help, very much appreciated.

    Cheers Rob.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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