Selecting all rows of data that have info in

[dpcp]

Hi all,

I am having some trouble adapting the following code to select rows of data:

Please Login or Register  to view this content.

Originally, it would have been enough to pull the top row of data from the "Ignore-this-sheet" worksheet.

Now, I need to adapt it.

Basically, how would I go about programming something like this:
select row 1 - copy, paste.
check if there is any info in row 2.
if there is info in row 2, then
select row 2 - copy, paste.
check if there is any info in row 3...
and so on.

I hope this makes sense.

Many thanks.

DP

[wamp]

try this:
it checks the last used row in "extractcs" sheet, then ammeds each row from sheet "ignore this sheet" that has a cell that contains value.

Please Login or Register  to view this content.

[dpcp]

Hi wamp

Thanks for the help

I get an error message:

Run-time error '9':
Subscript out of range

Please Login or Register  to view this content.

Perhaps this is because of the way my worksheets work -
I have worksheets in which the data is collected.
Then this data gets pulled into a separate Excel workbook, a "master" if you like (called "Master Template", and it is in the master file that all my macros are stored.

Maybe I just need to specify that it should be in the Master Template workbook?

[wamp]

if you get "subscript out of range" on the posted code, are you sure the sheetname is "extracts" ?

if you use another workbook, then I would change the code so it copies your relevant data to a temporary sheet, then copy it to the 'master' workbook or similar (a lot easier)

It would be a lot easier if you could you post an example of your workbook(s)?
and also in the workbooks point out what you're trying to achieve? (what goes where)

[dpcp]

Ok, still having no success, I am confused as to where to put a sort of "activate Master Template window" and then "select extracts worksheet", here is the code:

Please Login or Register  to view this content.

But I don't know how to edit the code properly....

[dpcp]

OK I must've been typing my reply when you were typing yours, I'll add my workbooks in the next post. thanks.

[dpcp]

Activity Survey 2010 ex.xls

Master Template ex.xls


The Activity Survey is what is being sent out for people to fill in. in the "Ignore-this-sheet" worksheet, their responses are put into one single row of data.

This row of data is then copied and pasted into the "Master Template", on which there is only one sheet titled "extracts".

The issue is that sometimes the surveys come back with three or four (or more) responses, and the original macro that i'd written only collects the first row of the data in the "ignore-this-sheet" of the surveys.

Since we are expecting about 400 responses, it would be a lot easier if the macro could pick up all relevant rows of data in the "ignore" sheet and paste them into the Master Template.

Many thanks for all the help.

DP

[wamp]

how about this then:
assumes that there is a value in A1, and also that the sheet-names are static.
copies the -values- from the form to the masterpage.
(code to be put in a module in the masterpage)

Please Login or Register  to view this content.

[dpcp]

The thing is, the Master Template has a macro which is basically used to open up all Excel files in a specified folder :

Please Login or Register  to view this content.

And so the CallData macro should only be about copying and pasting, a bit like this I suppose:

Please Login or Register  to view this content.

But the problem with the above CallData macro code is the

Please Login or Register  to view this content.

bit I suppose, which tells it to go back to the open survey window. Ideally, as in the ProcessAll macro, the xls files in a single folder should be opened, the correct rows copied over, and then the files closed. All done one by one.

[dpcp]

Ok, I have adjusted my "CallData" macro to the following:

Please Login or Register  to view this content.

But I get an error msg:

Run-time error '1004':

Method 'Range' of object '_Global' failed

and the debugger points to the line:

Please Login or Register  to view this content.

Any suggestions? many thanks!

[dpcp]

Anyone? Help much appreciated!

[dpcp]

Hello,

just to say I found a solution, and here is the code in case anyone else has a similar issue:

Please Login or Register  to view this content.

This ought to select right up to the last cell used in your worksheet, in the bottom-right so to speak.

DP