+ Reply to Thread
Results 1 to 4 of 4

Copying moving range based on moving cell reff.

  1. #1
    Registered User
    Join Date
    05-19-2011
    Location
    Naklua
    MS-Off Ver
    Excel 2007
    Posts
    9

    Copying moving range based on moving cell reff.

    Hi all,

    I am in a bit of a pickle on this one, so here it goes:

    I would like to copy a range from the 'Planning' sheet into a 'Forecast' sheet.
    In the planning sheet, column A is a mix of item numbers (e.g. 12-56-40) and week numbers (e.g. Week 2). The item numbers that are before the week number are a group of items for that week.
    The week numbers are ascending, but they do not necessarily start in week 1.

    The Forecast sheet only holds the week numbers from week 1 until 52 in every third column (A1:EZ1).

    When the macro is enabled, it should find the first week number in the 'Planning' sheet, and copy the data that belongs to the same week in column D and P, and paste it below the same week number in the 'Forecast' sheet.

    I have been around the world wide, and have only come up with the following:

    Please Login or Register  to view this content.
    Shortcomings: - 1 If there is a week in one of the sheets that is not in the other it will return an error -2 It does not copy the range that I would like (haven't figured out how to set the moving range on that one)

    It is properly completely the wrong approach to solve my issue, but it is the best I have come up with so fare from piecing together what I wound in forums.

    attached should be an example

    Thanks in advance
    Attached Files Attached Files
    Last edited by Varmark; 05-19-2011 at 04:29 AM.

  2. #2
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2003
    Posts
    4,168

    Re: Copying moving range based on moving cell reff.

    hi, Varmark, please check attachment, run code "test"
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-19-2011
    Location
    Naklua
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Copying moving range based on moving cell reff.

    Amazing! Thank you tons, works like a charm! I did think I was on the wrong track. I will spend the next couple of hours figuring out 'how' it works, cheers Watersev

  4. #4
    Registered User
    Join Date
    05-19-2011
    Location
    Naklua
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Copying moving range based on moving cell reff.

    One quick additional Q: If the data in 'Planning' column D is not numeric, but text. How could i get the VBA to work then. ?

+ 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