+ Reply to Thread
Results 1 to 8 of 8

Pasting to area depending on date

  1. #1
    Registered User
    Join Date
    02-19-2009
    Location
    Cambridgeshire, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    Pasting to area depending on date

    Dear All,

    Am a newbie who is also a complete VBA numptie and I am trying to write a macro that will copy some data from one page in an excel workbook and paste it to another page in the same workbook but the cell that the data is pasted to is dependent upon information in column A.

    Cell B2 contains today's date, column A contains a date range from 01.01.08 to 31.12.09 and Row 2 contains a line of static data detailing bank account numbers.

    I want to paste the balances on each account starting in column C on the row where the date in Column A is the same as the date in cell B2.

    So today is 23.02.09 - cell B2 is 23/02/2009 and in column A, cell A425 is 23/02/2009 so the macro should paste the data starting from cell C425.

    Tomorrow will be 24.02.09 so cell B2 will show 24/02/2009 and the data should be pasted into cell C426 as cell A426 shows the date 24/02/2009.

    A very kind person gave me the code below but this pastes the data in C3 rather than against the correct date in column A;

    Please Login or Register  to view this content.
    I originally used a formula to calculate the dates in B2 and column A but have copied and paste specialed them as values in case that was affecting the macro but I am now stuck again.

    Any help will be gratefully recieved.

    Thanks,
    DB
    Last edited by VBA Noob; 02-23-2009 at 02:04 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Pasting to area depending on date

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Pasting to area depending on date

    I have code tags for you. Can you attach a workbook with some dummy data in?

  4. #4
    Registered User
    Join Date
    02-19-2009
    Location
    Cambridgeshire, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Pasting to area depending on date

    Dear Roy,

    Apologises - please find attached the full piece of code and i ahve attached a dummy workbook as requested.

    Please Login or Register  to view this content.
    What I would like is for the data on the workbook page to be pasted onto the TC GBP Pooled page against the date in column A that is equal to the date in cell B2.

    Hope this makes sense and thanks for your help.
    DB.
    Attached Files Attached Files

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Pasting to area depending on date

    Use the =TODAY() formula instead of NOW(), then try this codeOption Explicit

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-19-2009
    Location
    Cambridgeshire, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Pasting to area depending on date

    Dear Roy,

    Thank you very much for your help - I have copied your code into my macro but when I run it, I get the following error message;

    Compile Error: Block If without End If

    Am not sure why this is coming up because I have

    Please Login or Register  to view this content.
    at the end of my macro so End If is definitely there!

    Thanks,
    DB

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Pasting to area depending on date

    have you copied & pasted the code to a standard module?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-19-2009
    Location
    Cambridgeshire, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Pasting to area depending on date

    Dear Roy,

    Thanks for your reply- wasn't able to get onto the website yesterday afternoon so apologises for the late reply!

    I have put your code into a macro that I recorded - full macro is here;

    Please Login or Register  to view this content.
    Have I missed something obvious?

    Thank you,
    DB

+ 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