Could use some help with the creating a macro that copies specific sheets (sheets are named) and then creates a new workbook and pastes those copied sheets in that workbook. Then the macro should save the new workbook. I've written the code as follows:
What my code does at the beginning is ask the user what month and year the data is related to. After which it goes about copying the specified sheets (VMS Methods/Export Flowrate/Well tuning Parameters) and SHOULD create a new workbook with these sheets in it. It then should save the workbook with the file name that includes the Month and Year. However, it crashes out with a subscript out of range error at following line:
I think this happens because the active workbook becomes the NEW workbook, which essentially doesn't have the other sheets so excel can't continue with the macro. I just don't know how to get around it. In addition, if I comment these out and just copy for the first sheete ("VMS Methods"), excel still asks me for to name the file when doing "Save As". If I specify a file name for save as, it saves the file and closes the workbook but the file has no extension!!
Any help would be much appreciated!