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

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your Register and Transmit sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Dave Carr QM]

Unable to add XL2BB so I have uploaded the Excel file to We Transfer if anyone can help.

https://wetransfer.com/downloads/fb0aca6591f42dbc033d0210043f7f0020240712150237/92885e6eda10c86fe9d559c8545c623a20240712150237/839a52


Process is
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 a new sheet Output sheet "001"

If Button B is pressed the same process is carried out but completing "Transmit B" and saving that sheet the same as Transmit A

Thanks

 

[mumps]

How do you determine the range to be copied? In your example, you copy four rows of data. Why not 5 or 6 rows?

 

[Dave Carr QM]

How do you determine the range to be copied? In your example, you copy four rows of data. Why not 5 or 6 rows?

The range is determined based on the completed cells on the column where the button is pressed in this example it is cells S11-S14 which determines the rows 11-14 are copied . If button B was pressed for date 11/4/24 then rows 13 & 15 would be selected.
Normally the transmittals would be sent in order from column P sequentially.

Hope that helps

 

[mumps]

Is there any reason why you have two "Transmit" sheets. They appear to be the same. Also, having all those A and B buttons would mean that you would need to program them separately. Are you open to using a different approach without the buttons?

 

[Dave Carr QM]

Is there any reason why you have two "Transmit" sheets. They appear to be the same. Also, having all those A and B buttons would mean that you would need to program them separately. Are you open to using a different approach without the buttons?

Hi Mumps
The only reason for Button A & B is that the Document Controller did not want Transmittal notes generated with their name on the document by other staff, hence the 2 Transmittal forms.
If there is a way of generating the transmittal with the following questions
Date of revision to send?
Are you document controller ? If Yes, then enter password If No generate Transmittal without Document Controller details
Welcome any suggestions

Thanks for your interest in this thread.

 

[mumps]

I'm sorry but I don't follow. In your original request, you didn't mention the copying of the Document Controller details. By "Document Controller details" do you mean the data that would go in B6 and B8 of the Transmit Form? If so, where is that data found on the Register sheet? Does the revision date go in E6 of the Transmit Form? Was the purpose of the A and B buttons, for the A buttons to include the Document Controller details and the B buttons not to include the Document Controller details? Are you open to using a different approach without the buttons?

 

[Dave Carr QM]

Sorry for not explaining very well
Cells B6 & B8 will be completed manually on the template depending on the project to which the register applies. NO DATA COPY REQD
The only differences between both versions of the Transmit forms are cells B36 & E36 In the Transmit A ( Document Controller version) cells B36 & E36 will be prepopulated with the name and signature of the document controller.
In Transmit B these cells will be manually entered by the person issuing the documents.
The Only reason for separate forms is the document Controller does not want any Transmit forms sent with there signature and name which they have not approved.

Certainly open to any approach, buttons are not essential.

Hope that makes things clearer

 

[mumps]

Date of revision to send?

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?