+ Reply to Thread
Results 1 to 7 of 7

Smartest way to copy data from other worksheet

  1. #1
    Registered User
    Join Date
    09-21-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    71

    Smartest way to copy data from other worksheet

    Hi guy's,

    First of all I think I know a way how to do this, I just don't know whether it is the smartest way.

    I've got data in cell A5 to cell ALastRow (for instance A40). Same for B5 to BLastRow, C5 to CLastRow. Well you get the picture.

    This data must be copied to another worksheet if cell E or F contain data. If cells E and F are empty the data must not be copied.

    My plan:

    In the worksheet where the data must be copied to, I will pass a variable which indicates the LastRow. That way I know when I have to stop checking cells E and F.

    I will write a loop which activates the source worksheet, checks cells E and F and, if neccesary, stores the content in variables. Then I will active the target worksheet and write the content to the appropriate cells.

    The loop will keep going until it has reached the last row of the source worksheet.

    Is this a good approach or is there a more efficient way to go about this?
    Last edited by Jeroen1000; 12-15-2009 at 03:40 AM.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Smartest way to copy data from other worksheet

    It will work, but AutoFilter or Advanced Filter would probably be more efficient.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Smartest way to copy data from other worksheet

    I would suggest the same as Stephen though given the nature of your setup - ie E & F requirement you may need to copy the entire range (to F) and apply the filter on the copy, ie along the lines of:

    Please Login or Register  to view this content.
    obviously change sheet references as necessary... and with the obvious caveat that this is only a suggestion which may / may not be viable based on specifics otherwise not outlined (ie assume row 5 is headers... and that A:F can be copied in the first instance!)

  4. #4
    Registered User
    Join Date
    09-21-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    71

    Re: Smartest way to copy data from other worksheet

    I can't seem to get it to work. I am doing something wrong but can't see what it is. It only loops once and then just finishes

    Please Login or Register  to view this content.
    A little info: "Verschillenlijst" is the targetsheet. Originalsheet is the source. LastRow contains a variant which holds a number (in this case 42). i starts at 6 because this is the first row of originalsheet which may or may not contain data.
    Doelrij (translated TargetRow) is the first row of the target sheet where data must be put. I know I still need to select a column but this is more like debugcode.


    @DonkeyOte, wow that looks really complicated. Furthermore, the layout of the source and targetsheet is not the same. I'm just going to check the source sheet row for row whether E and F is empty, and if it is not, stuff the data I need from the other rows into variables and write them to the correct places in my target sheet. And even that is giving me a little trouble as you can see
    Last edited by Jeroen1000; 12-08-2009 at 09:00 AM.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Smartest way to copy data from other worksheet

    Realistically to get optimised code you should outline "what" goes "where" - best to work in blocks wherever possible and always avoid Select / Activating objects where possible - you rarely need to and it slows your code.

    In short your loop code probably be conducted along the lines of:

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Smartest way to copy data from other worksheet

    after reading you post i though i would have a crack at traning myself.

    seams you have got some great answers from other members on this aswell.
    Vba is great for allowing many possible ways of approaching problems. I have tended to use parts of this code in a few diffrent ways.
    Please Login or Register  to view this content.
    hope you get some use out of it, and good luck on getting it sorted.

    cheers


  7. #7
    Registered User
    Join Date
    09-21-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    71

    Re: Smartest way to copy data from other worksheet

    I'll be studying the code you both gave me (thanks for that BTW) as it is clearly more advanced than mine. I will have to start to understand how to write more efficient code as I can notice a slowdown when executing my macro.

    Nevertheless, for the time being, I decided to puzzle something together myself (there used to be times I could'nt do any of this so I'm kind of pleased with it).

    I've changed the comments to English and I'll try to go into more detail about what it does.

    Please Login or Register  to view this content.
    - Alright, first the variables LastRow and Originalsheet. The first variable is the last row of the source sheet which contains data. If that row contains data all the others above it will too. Originalsheet is the name of the source sheet. The first row that can contain data (for the source sheet that is) is row 6 and is hard coded (see the for loop).

    - The target sheet contains titles here and there so that the first row which is allowed to receive data is row 11. I've called the variable Doelrij (target row). 1 is added to Doelrij if something was written to the target sheet. Makes sense because otherwise I'd have empty rows in between.

    - I only need to copy data if there is something in either column E or F. If both are empty nothing needs to be copied.

    The rest will probably be childsplay for you guy's to understand

    I know the code probably sucks but I'm going to try to write it in different ways using your examples. Time to study
    Last edited by Jeroen1000; 12-09-2009 at 04:16 AM.

+ 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