+ Reply to Thread
Results 1 to 15 of 15

Macro to copy and paste cells on multiple sheets based on dates

  1. #1
    Registered User
    Join Date
    08-20-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    15

    Exclamation Macro to copy and paste cells on multiple sheets based on dates

    Hi All,

    I currently am running a spreadsheet with hundreds of sheets each representing an agreement.

    These sheets contain all instalments, past current and future.

    Every month I copy the last month's instalment, paste it to the cell below it (next months) and then run a goal seek to distribute the differences in interest calculation amongst the remaining instalments.

    Up till now I copy-paste on every sheet manually and this is what I have created to make my life easier after I have done that:

    Please Login or Register  to view this content.
    Is there a way to create a macro that will search for a date (say "2013/07/25") in column "A" then copy the cell to its left and paste it one cell below?

    The rest would be simply to add what I already have in the macro to do the goal seeking until the macro reaches the last sheet...

    Example:
    Example snippet.PNG

    Thank you in advance :-)

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

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-20-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Quote Originally Posted by AB33 View Post
    Please Login or Register  to view this content.
    Thanks AB33 for your reply.

    However the data is being copied to the wrong cell - as I am not familiar with this language I refrain from trying to edit your code myself.

    Here is a snapshot of what happened when running your code:
    Error snippet.PNG

    Thanks for your help so far! :-)

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

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Please do not reply with quote, just a reply will do.

    If you are searching column A, you can not go to the left of column as there is none.
    My code goes to the right.
    If the code finds, let say in A2, one to the right of A2 is B2 and where do you want to copy B2 in to?

  5. #5
    Registered User
    Join Date
    08-20-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Apologies, I meant to the right.

    If it is found in A2 then I need B2 to be copied to B3.

    If you look at my snapshot your macro is copying it to A3 (in the above scenario).

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

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-20-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    15

    Unhappy Re: Macro to copy and paste cells on multiple sheets based on dates

    Hey,

    So now it's copying the cell to right of the found date to one column right.

    For example if the date is found in cell A2 then B2 is copied to C2.

    Error snippet 2.PNG

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

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Sorry,
    I can not get my coordinator right
    Change this line of the code

    Please Login or Register  to view this content.
    INTO
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-20-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Perfect! works like a charm :-)

    now how can I put this in a while loop with a command to go to the next sheet till its reached the last sheet in the workbook?

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

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Do you mean you want to apply it across sheets, instead of a single sheet as it does now?

  11. #11
    Registered User
    Join Date
    08-20-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Yes, that is correct.

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

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Mind you this code is untested

    Please Login or Register  to view this content.
    Last edited by AB33; 08-21-2013 at 05:55 PM.

  13. #13
    Registered User
    Join Date
    08-20-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Macro to copy and paste cells on multiple sheets based on dates

    "Runtime error '438'
    Object does not support this property or method"

    When debugging it highlights "rFind.Offset(, 1).Copy .rFind.Offset(1, 1)"

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

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Yes, I have corrected it. Look at the code again.

  15. #15
    Registered User
    Join Date
    08-20-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Macro to copy and paste cells on multiple sheets based on dates

    Can I add a Goal Seek option that will be done per sheet that the date is found as follows:

    Please Login or Register  to view this content.

+ 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] Copy and Paste Value macro on multiple sheets
    By Elmarie_Potgieter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2013, 02:15 AM
  2. [SOLVED] Macro to copy and paste based on criteria to 11 separate sheets
    By msmith7113 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2013, 02:30 AM
  3. Macro to copy specific color cells frm multiple sheets & paste link to other similar wrkbk
    By rahulmalhotra in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2013, 11:23 AM
  4. Excel Macro to insert two rows based on condition and copy and paste multiple cells
    By mannabhalo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-13-2012, 12:56 PM
  5. copy cells from sheets based on date, paste to another sheet
    By upod in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-27-2009, 01:29 PM

Tags for this Thread

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