+ Reply to Thread
Results 1 to 6 of 6

5 steps macro to copy data to new worksheet

  1. #1
    Registered User
    Join Date
    09-16-2010
    Location
    Glasgow
    MS-Off Ver
    2010
    Posts
    68

    5 steps macro to copy data to new worksheet

    Hi everyone,

    I would need help to create a macro which could implement the five steps below, however I am not too sure if that is possible at all. Attached is the spreadsheet which contain the 'Staging Jobsheet-Template' worksheet (original data) and the new worksheet (i.e. the outcome following macro).

    Explanations of the 5 required steps:

    - First step:

    1) Add a new worksheet

    - Second step:

    2) In the 'Staging Jobsheet-Template' worksheet, search for the first non-empty cell in column D (this will be the first row of relevant data)
    3) Starting from the row where the first non-empty cell was found (in this case row 16), copy columns A, c and D of the 'Staging Jobsheet-Template' worksheet respectively to columns D, F and E of the new worksheet when the following conditions are met:
    a) Cell from column A is never emtpy
    b) Cell in column C is equal to a number between 1 and 10

    - Third step:

    4) Search for content of cell E1 from the new worksheet in the 'Serial No.' row of the 'Staging Jobsheet-Template' worksheet (in this case FCZ150475AA)
    5) Once the value has been found, copy the associated Temporary Device Name (i.e. the cell located 8 rows above the cell found in previous step), in this case VPN22457R1
    6) Paste the value in the cell located 2 columns to the left of E1 in the new worksheet (in this case cell C1 of the new worksheet)
    7) Carry on step 4 through 6 for all values present in column E of the new worksheet

    - Fourth step:

    8) In the 'Staging Jobsheet-Template' worksheet, search for cell entitled 'Site ID' in column A
    9) Locate and copy the first cell located to the right of the 'Site ID' cell (in this case copy value 11111)
    10) Paste the value in column B of the new worksheet for all rows having a value in column D

    - Fifth step:

    11) In the 'Staging Jobsheet-Template' worksheet, search for cell entitled 'Site Address' in column A
    12) Locate the first cell located to the right of the 'Site Address' cell (in this case 201-203 MAN,BRADGATE,BP9 4RT,ENGLAND)
    13) Paste the value in column J of the new worksheet for all rows having a value in column D


    Thanks a lot for your help,
    Antoine
    Last edited by Folshot; 06-14-2011 at 04:22 PM. Reason: Added [SOLVED]

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: 5 steps macro to copy data to new worksheet

    Good thing you didn't ask for too much.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-16-2010
    Location
    Glasgow
    MS-Off Ver
    2010
    Posts
    68

    Re: 5 steps macro to copy data to new worksheet

    Thanks so much stnkynts, that's really appreciated. I have been trying a few things in the last few days, but had issues with part 2.

    I had to slightly modify part 2 as I required all parts to be listed, ie even if they do not have a serial number. Hence part 2 is now:

    Please Login or Register  to view this content.
    Hence I had to change the column reference in part 3 to find the correct last row:

    Please Login or Register  to view this content.
    Now I get nearly what I need, but I have 2 remaining issues. It is probably better explained through the attached screenshot Staging-Jobsheet-Template-Macro.jpg, ie:

    1) The serial numbers are not at their correct location in column E. It seems that the following statement does not like when it finds blank cells:

    Please Login or Register  to view this content.
    Is there a way to copy the cells from columns A, C and D of the source worksheet in one go?

    2) The temporary name only appears for the first serial number, not for the remaining ones.

    Not sure why that one does not work.

    Thanks again,
    Antoine

    PS: I have attached a new spreadsheet with the modified macro:

    Staging Job Sheet 1.3.xlsm

  4. #4
    Registered User
    Join Date
    09-16-2010
    Location
    Glasgow
    MS-Off Ver
    2010
    Posts
    68

    Re: 5 steps macro to copy data to new worksheet

    Just found out that the issue with the temporary name not being populated (i.e. part 3 of the macro) was due to leading empty spaces in the serial numbers.

    I just added the following code to the macro to trim all cells before starting the macro:

    Please Login or Register  to view this content.
    Hence I have only one remaining issue with the serial numbers not being at the correct location (i.e. part 2 of the macro).

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: 5 steps macro to copy data to new worksheet

    By removing the data validation of ensuring column D has a value it threw everything off since the concept is based upon just offsetting the transferred values by 1. Off the top of my head it would probably be easiest just to throw in a space into the cell if there is no serial number in sheet 1. The offset will recognize the space and paste below it so everything should line up.

    I have added in your portions of code to the new code below. Please review for completeness. In addition, for the loop that you supplied it is usually best to avoid the use of just “cell” as a variable since “cells” is a property unto itself. Most people would suggest something like icell or acell or in this situation I changed it to trimcell.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-16-2010
    Location
    Glasgow
    MS-Off Ver
    2010
    Posts
    68

    Re: 5 steps macro to copy data to new worksheet

    Sorry for the late feedback stnkynts, your suggestions are spot on, it works perfectly. Thank you so much!

+ 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