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?

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!)

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

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.

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

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