Results 1 to 7 of 7

Macro to move cells (Col B to Col V) to other sheet. Place line number in col A

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Macro to move cells (Col B to Col V) to other sheet. Place line number in col A

    Hi
    I need a macro that would move information from one sheet to another.
    It should take all the information of cells in column B to column V (vertical length of information as per column B). Now it should take this information from one sheet called "delivery data" and place it in first available row in sheet called "Slipped dates"
    Further more I would like the macro to put number in column A. That number should be like count number e.g. 1, 2, 3, 4, 5, etc. This would give me an opportunity to always arrange the lines in the order they were originally added. So if someone will arrange the data by part number or by some other column, then there is a way to re arrange all the data back to in which way the were added to this sheet. Last but not least I would like the macro to do a simple date difference calculation in column W and column X. Column W should equal column N minus Column I. Column X should equal column N minus Column J.

    I will give an example (in the example I will only have the relevant columns of this range col B to col V)

    Sheet "delivery data"
    Col A ------------------- col B ------------- Col I ------------ Col J ---------- Col N --------- Col V
    empty cell --- 181026979610 --- 19/04/2011 --- 20/04/2011 --- 22/04/2011 --- 05BN
    empty cell --- 181026981010 --- 19/04/2011 --- 20/04/2011 --- 22/04/2011 --- 05BN
    empty cell --- 181026982010 --- 19/04/2011 --- 20/04/2011 --- 21/04/2011 --- 05BN
    empty cell --- 181026982310 --- 19/04/2011 --- 20/04/2011 --- 21/04/2011 --- 05BN


    Sheet "slipped dates" before macro (lets say we already have some data on this sheet)
    Col A ------------------- col B ------------- Col I ------------ Col J ---------- Col N --------- Col V ---- Col W --------- Col X
    1 ---------------- 181026993410 --- 19/04/2011 --- 19/04/2011 --- 27/04/2011 --- 05BN -------- 8 ------------ 8
    2 ---------------- 181027008110 --- 19/04/2011 --- 19/04/2011 --- 22/04/2011 --- 05BN -------- 3 ------------ 3
    3 ---------------- 181027588810 --- 19/04/2011 --- 19/04/2011 --- 23/04/2011 --- 05BN -------- 4 ------------ 4


    Sheet "slipped dates" after macro
    Col A ------------------- col B ------------- Col I ------------ Col J ---------- Col N --------- Col V ---- Col W --------- Col X
    1 ---------------- 181026993410 --- 19/04/2011 --- 19/04/2011 --- 27/04/2011 --- 05BN -------- 8 ------------ 8
    2 ---------------- 181027008110 --- 19/04/2011 --- 19/04/2011 --- 22/04/2011 --- 05BN -------- 3 ------------ 3
    3 ---------------- 181027588810 --- 19/04/2011 --- 19/04/2011 --- 23/04/2011 --- 05BN -------- 4 ------------ 4
    4 ---------------- 181026979610 --- 19/04/2011 --- 20/04/2011 --- 22/04/2011 --- 05BN -------- 3 ------------ 2
    5 ---------------- 181026981010 --- 19/04/2011 --- 20/04/2011 --- 22/04/2011 --- 05BN -------- 3 ------------ 2
    6 ---------------- 181026982010 --- 19/04/2011 --- 20/04/2011 --- 21/04/2011 --- 05BN -------- 2 ------------ 1
    7 ---------------- 181026982310 --- 19/04/2011 --- 20/04/2011 --- 21/04/2011 --- 05BN -------- 2 ------------ 1



    Now as far as it concerns the line number in column A. If someone arranges the data by some column it might mess up the original order. Lets say the original order:
    1
    2
    3
    4
    5

    might be scrambled like this
    4
    2
    5
    1
    3

    In this scenario the next line should bear number 6 and not number 4.
    I think there might be 2 options to solve this problem. First option would be to use row number minus 1 (as there is a header row). As the next available line would be on row 7 on the spreadsheet we would get 7-1=6
    Second option would be a be a macro that would loop through column A to find the highest number which in this example would be number 5, and then add +1 to it.

    I have attached spreadsheet with sheets "delivery data", "slipped dates before" and "slipped dates after" to see how it should look before the macro run and after the macro run.


    Thank you someone has time to help me with this macro.

    Cheers
    Rain
    Attached Files Attached Files
    Last edited by rain4u; 05-02-2011 at 04:45 PM. Reason: Changed the title

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