Copy data from one workbook to a master workbook w/ multiple worksheets by a Submit button

Hi, I have been struggling with how to copy data from one workbook to a master workbook that has 4 worksheets. Based on the criteria of Column "G" (29, 50, 75, or 100), I need to have the entire row of data copied into the specific worksheet of the master workbook (the worksheets are already named). The data needs to be pasted on the next blank row of each worksheet. I have a working code for the data to be sent to the master workbook, but do not know how to make the multiple criteria work.

Also, I need to be able to click the submit button whether the master workbook is opened or closed (right now it is only working when closed.) Any help would be greatly appreciated! Here is what I have so far:

I am working with excel 2013.

Please Login or Register  to view this content.

Uploading the workbook you are trying to work with could be helpful.

Please Login or Register  to view this content.

To answer your first question ... use an if then statement to wrap the equate values command e.g.

Please Login or Register  to view this content.

Now the row will only be transferred if G2 = 100 ... you can name any rule you want

I would like to be able to use the submit button whether the master workbook is opened or closed

I would do something like the following ...

Please Login or Register  to view this content.

To use the above code add the following function to the bottom of the module

Please Login or Register  to view this content.

If you would like to close the destination workbook (after the code has run) if it was previously closed and needed to be opened or do nothing if it was already opened this can be done to. Simply insert a variable declared as Boolean after the Workbooks.open command and set it to true ... something like:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

contains uses wbDest which has not been set yet. Place the line after the if then statement

Please Login or Register  to view this content.

bIsBookOpen is a function which tries to set the workbook variable to an "open" workbook. Obviously if the workbook is not open it will return an error. Below is the function which you need to copy and past at the bottom of your module or in an entirely different module....your choice.

Please Login or Register  to view this content.

Also

Please Login or Register  to view this content.

needs to be changed...I don't think you can declare a worksheet variable to all the worksheets in a workbook. eg

Please Login or Register  to view this content.