VBA Help - Command Button to copy data from one sheet to another

[Dave Carr QM]

As a new member I am impressed with this forum, my first post was solved in minutes.

I have no experience with VBA code so I am at a loss on where to start with my next Problem on the same spreadsheet.
I have a Document Register which I want to link to a Transmittal sheet with the press of a command button, the process is as follows, when referenced to the displayed model
Documents are listed on the Register as they are completed by the designer, If the Document controller presses button A under a specified date ( highlighted yellow) then cells C11:C14 are copied into sheet "Transmit A" cells B13:16, also D11:D14 copied to C13:C16, and S11:S14 copied to D13:D16.
Other Cells on "Transmit A" could be filled Manually
When the Save Button is Pressed the sheet is saved as an Output sheet "001"

If Button B is pressed the same process is carried out but completing "Transmit B"

Any Assistance would be grateful

 

[Dave Carr QM]

Is this the date above the buttons in the Register and if so, does this date go into E6 of the Transmit form? I could put a date picker in E6 and E40 so you could simply select the date from a pop up calendar instead of entering it manually. Would it work for you if the Transmit A sheet were hidden and could be made visible only if a password was entered by the document Controller?

Yes the date (in example) S3,4,5 goes into E6 only and is the selection date for the process to fill Transmit document.
Cell E40 is manually completed by person receiving the document transmit, and approving the documents.
A pop up calendar would be OK
Transmit A is best hidden

Thanks

 

[mumps]

When the Save Button is Pressed the sheet is saved as an Output sheet "001"

Does this mean that if a second Output sheet is created, it will be named with "002"? Once the Output sheet is created, do you want to clear all the data in the Transmit sheet to be ready for the next Output sheet? Do you want the Transmit A sheet to remain visible after the password is entered?

 

[mumps]

Waiting for your response to my last post. In the meantime, click here to download your file.
Right click the tab name for your REGISTER sheet and click 'View Code'. Near the top of the code, you will see a temporary password ("MyPassword"). Change this password to suit your needs. Close the code window to return to your sheet. You will notice that the buttons are gone, replaced by drop down lists which contain either an A or a B. Simply select one or the other to copy the data. The "Save" buttons on the Transmit sheets will create the Output sheets. A calendar pop up will appear when clicking on E40 on the Transmit sheets.

 

[Dave Carr QM]

Does this mean that if a second Output sheet is created, it will be named with "002"? Once the Output sheet is created, do you want to clear all the data in the Transmit sheet to be ready for the next Output sheet? Do you want the Transmit A sheet to remain visible after the password is entered?

Sorry away from Internet yesterday.
Yes the output sheet transmittal number will be incremental from last generated number
Yes Clear data
Transmit A and B can be hidden sheets ? only becoming visible when a new sheet is created,
Output sheets need to be visible as a new tab once created.

I will look at your download, thanks for your support and interest on this.

 

[mumps]

Click here for your file. Please note that cells AT1 and AT2 in the REGISTER sheet are being used as helper cells to keep track of the naming of the Output sheets. They are both currently equal to "001". Each time you click the "Save" button, the appropriate cell is increased by 1.

 

[Dave Carr QM]

Hi Mumps
Great Work- Thank You
However there is an issue in the copy of the revision into the Transmittal, this may be down to my explanation.
I have generated 2 forms I will try and explain below

When selecting Transmittal B on column U the two file names and detail are correct the revision should be P03 as indicated in cells U11 and U16
When selecting Doc Controller on Column V the two file names and detail are correct the revision should be P03 and P02 as indicated in cells V13 and V15

Also can the Output file be named 61905 - xxx ( where 61905 is the Project number and xxx is the Transmittal no) This number should also be in cell E8 of the Output

 

[mumps]

Click here for your file. I have re-formatted E8 of both Transmit sheets to "General". I assume that The Project number is in D4 of REGISTER and that the Transmittal Number is in D37.

Also can the Output file be named 61905 - xxx ( where 61905 is the Project number and xxx is the Transmittal no)

Please note that there cannot be duplicate sheet names so each time the "Save" button is clicked, either the the Project number or the Transmittal number or both have to be different from the previous time the "Save" button was clicked .

 

[Dave Carr QM]

Click here for your file. I have re-formatted E8 of both Transmit sheets to "General". I assume that The Project number is in D4 of REGISTER and that the Transmittal Number is in D37.

Please note that there cannot be duplicate sheet names so each time the "Save" button is clicked, either the the Project number or the Transmittal number or both have to be different from the previous time the "Save" button was clicked .

Hi Mumps
Thats Excellent, Thank You for your hard work on this.

 

[mumps]

You are very welcome.

 

[Dave Carr QM]

Hi Mumps

Updated file can be found on We Transfer https://wetransfer.com/downloads/0b7b0519444a5fced5aedf4a2306eb5920240722110942/2db4d01af47e83b4c4d371c3f271bd8c20240722110942/fd5aa5

I am sorry I changed some rows on the Transmittals and added some drop down lists and now I have the following errors

1 For some reason Both transmit forms copy row 14 file details when it does not appear in P14 or R14
2 The Save button does not work.

Note the Project Number D4 will always be 61905 but Transmittal number will be incremental and unique.
Can cell C10 & E10 on the Transmittal be copied to E37 & I37 (if Transmittal 1)

As the document grows how easy is it to add rows beyond 21 & 39 and columns AF onwards without corrupting the VBA?