+ Reply to Thread
Results 1 to 7 of 7

Using current month selection to copy/paste formulas and values

  1. #1
    Registered User
    Join Date
    01-03-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Using current month selection to copy/paste formulas and values

    Hi all

    I've got an issue that has stumped me that I would really appreciate some help with!

    The below graphic might give you some idea of what I am trying to achieve. This is just a basic example. D4 downwards has the formula =$C4*2, =$C5*2 etc etc

    Capture.JPG

    I want to be able to use a drop-down list which drives the vba code that has a two-step process. The first step being that once the code determines what month I have selected, it will copy and paste the formula from the prior month into the next month. Then the next step is for the prior month data to be 'locked-in' by copying and pasting values over the top of the old formula.

    So, if I select 28/2/2014 above, I want January's formula pasted into cells E4:E13 and then January's values copied and pasted into D4:D13 and so on.

    If I haven't provided enough information then please let me know!

    Cheers

    Gigi

  2. #2
    Forum Contributor
    Join Date
    11-12-2013
    Location
    Delhi
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Using current month selection to copy/paste formulas and values

    pls share ur excel sheet....

  3. #3
    Registered User
    Join Date
    01-03-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Using current month selection to copy/paste formulas and values

    Sorry it's a very rough replica of the file I'll be working with. Apologies for the text boxes and merged cells!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Using current month selection to copy/paste formulas and values

    Maybe something along the lines of:
    Please Login or Register  to view this content.
    in the worksheet object module - you will need to unmerge the cells in column E first though.

  5. #5
    Registered User
    Join Date
    01-03-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Using current month selection to copy/paste formulas and values

    Thanks yudlugar - silly question but does this entirely replace the code that I had in module 1? When I try run sub I just get a list of the Macros...or if I paste into existing code I get a compile error. Sorry still trying to get my head around vba - I'm very raw!

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Using current month selection to copy/paste formulas and values

    Hi - just delete module1. The code is an "event", so it is triggered by something happening in excel. The event name is:
    Worksheet_Change
    So, it is triggered by something on the worksheet changing.

    Event macros need to go in the vba object module for what they need to apply to, so in this case, the sheet1 object. In the project explorer, you shoudl see two folders in your example, one for modules and one for microsoft excel objects. In the microsoft excel objects there will be a different module for each sheet, and one for the workbook. Open the module for sheet1 and copy the code into it.

    The code will then run automatically whenever you change the worksheet, this part:
    Please Login or Register  to view this content.
    tells it only to run the rest of the code if the cell you changed is D1.

  7. #7
    Registered User
    Join Date
    01-03-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Using current month selection to copy/paste formulas and values

    Oh nice - that's awesome, thank you so much for your help!

+ 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. [SOLVED] Changing Current Code from Paste Formulas to Paste Values
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-24-2013, 12:33 PM
  2. Vba - find next empty column, insert column, copy paste values & copy paste formulas
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2012, 12:43 PM
  3. Replies: 0
    Last Post: 09-18-2012, 11:47 AM
  4. Excel Button to copy selection and then paste special values over it
    By transportplanner in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-14-2011, 07:00 AM
  5. [SOLVED] copy worksheet from previous month and rename to current month
    By Dan E. in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2005, 05:45 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