copying data with VBA

[alec1986]

hi everyone!

ok so here is a very basic explanation of what if would like to do:

in a workbook with multiple sheets, i would like to extract certain rows of data onto a summary sheet. whether or not the information is extracted will depend on a number in column "E". the hoped for response is that all title, non-complete or ref data will be ignored while all the data i require will be copied over! sounds simple enough...

check worksheet 1, column E, for digits.

Copy that entire row if a digit is found, to a "mastersheet".

Repeat for all worksheets (obviously without the mastersheet).

Stop when complete.

This is a piece of code i have been given:

Please Login or Register  to view this content.

i understand aspects of this, but i would love to know how this might be modified for my requirements.

Any help would be amazing!

Alec

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[alec1986]

Thanks, Learning Constantly today!

Alec

[alansidman]

Try this code. It is untested as you did not provide a spreadsheet with data.

Please Login or Register  to view this content.

[alec1986]

great, thanks so much, the code does work, however it is still pulling data from those rows that are title pages etc. a screen print of the output (pretty much exactly the same as the input) can be seen below:

Example screen shot.jpg

Would it have anything to do with the merged cells on the sheets?

Alec

EDIT: Image link does not seem to work.

[alec1986]

great, thanks so much, the code does work, however it is still pulling data from those rows that are title pages etc. a screen print of the output (pretty much exactly the same as the input) can be seen below:

Example screen shot.jpg

Would it have anything to do with the merged cells on the sheets?

Alec

[alec1986]

link try again...

https://www.dropbox.com/s/i1meaily7o...0shot.jpg?dl=0

[alec1986]

I would attach the spreadsheet but it is massive.

[JOHN H. DAVIS]

What do you mean by digits? Try:

Please Login or Register  to view this content.

[alec1986]

Ok, so i have taken the unmolested original version on the spreadsheet here. it is much smaller, all the playing about with it had made it unmanageble and unresponsive. hopefully this will give everyone a better understanding of what i'm trying to achieve.

in column "E" of all the worksheets except master RDS, there is a "QTY" column. i would like to retreive this data and add it to a huge list on the Master RDS. currently, changing the "digits" portion currently in the code to a number (for example, 2) still retrieves ALL data, regardless of whether that data actually has a two in it. i am placing the code within the Master RDS sheet in the macro editor.

EDIT: ok so apparently even the pared down superleggera version of this spreadsheet is too large! (1000kB Limit!)

any other way i might be able to send it, just to give anyone a better idea of what is happening? ive managed to knock it down to two source sheets and the summary sheet, comes in at 1.47MB...

Alec

[alec1986]

Update:

I've managed to get it to populate a list by exchanging "digits" for 2. is there any way i can get it to do ANY whole number?

[JOHN H. DAVIS]

Maybe?:

Please Login or Register  to view this content.

[alec1986]

thanks John,

This one populates the list with ALL data again. i'm starting to wonder whether the merged cells are causing problems?

I have organised a Workaround, where taking the code you gave earlier and exchanging the "Digits" for a "2", it will populate the list with everything with a 2 in the E column. i can get it to populate the list with any integer, and i could in theory populate the list by just swapping each number out and running the macro. i would really love to know how i could get it to do any number tho! can you string numbers together, i.e "1,2,3,4,5,6"? not very elegant, doesnt seem to work for me, but can it be done?

thanks once again for your help, great forum!

Alec

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[alec1986]

Perfect!

Thanks so much! just managed to populate a list 13,966 rows long in no seconds! have become office king. and managed to learn a fair bit in the process.

Thanks again to everyone who had an input, no doubt i will be back soon.

Alec