Help getting data from one worksheet to the next.

Closed
amsterp Posts 3 Registration date Tuesday February 12, 2019 Status Member Last seen February 12, 2019 - Updated on Feb 12, 2019 at 11:46 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 14, 2019 at 11:55 AM
Hello,

I am trying to set up a master job sheet for all of our field employees to have the information in the field. The links to the 2 sheets are below. The blank sheet will be opened then all the information filled in and saved as the job name. I want to automatically move that entered information onto the master list, so would need to move to next blank row then paste the information in the corresponding columns. This lets me print each "job Sheet" for the file, and then have a running list to print for distribution in the filed.

Can someone please help!!

Thanks!


https://www.dropbox.com/s/9166hujfuj7hgnh/AAA%20Job%20List.xlsm?dl=0
https://www.dropbox.com/s/z0m8aijuklfiqjm/AAA%20Blank%20Job%20Sheet.xlsm?dl=0


System Configuration: Windows / Chrome 71.0.3578.98

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 12, 2019 at 11:46 AM
Hi Amsterp,

I don't understand the need for the second link.

Assuming I understand you correctly, I would create a hidden template sheet for 'Job sheet' along with an empty 'Job sheet' and 'Master list'.
Then enter the data in the empty 'Job sheet'. When you click Print a macro could do the following:
- Print the filled in 'Job sheet'
- Transpose the data from filled in 'Job sheet' to 'Master list'
- Rename the 'Job sheet'
- Copy the template to create a new empty 'Job sheet'

Try recording a macro to get you started.

Is this what you are after?

Best regards,
Trowa
0
amsterp Posts 3 Registration date Tuesday February 12, 2019 Status Member Last seen February 12, 2019
Feb 12, 2019 at 11:56 AM
I will go into more detail. We are a construction company so when our estimators are preparing the estimate they would fill all the information on the blank job sheet at time of estimating.

If and when we are awarded the project we would open that job sheet and then move it to the job list for distribution in the field.

We want to keep each job it's own file in case we need to make changes because in the construction field the owners may change their mind on a project numerous times, this way we can just open that file, make the changes and save it. Once we are awarded we can open the file the owners decided to go with and move that information to the master list.

Thanks!!!
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 12, 2019 at 12:15 PM
Hmmm, then I don't understand the need for job lists/sheets. Why not just create a master sheet. This will create an overview for all the jobs that needs to be done and when changes need to be made, you don't need to look up the individual job sheets.

I doesn't make sense to me to have a 6 files (when the project has 6 jobs for example) with 1 job sheet each. Then have another file with a copy of each of the job sheets (now called job lists?) and a master list.
0
amsterp Posts 3 Registration date Tuesday February 12, 2019 Status Member Last seen February 12, 2019
Feb 12, 2019 at 12:21 PM
The owner wants this, so I have to find a way!
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Updated on Feb 14, 2019 at 11:55 AM
Ok, so I think there are a few steps involved:

- Moving the individual job sheet files to the master list file.
This is done by right-clicking the sheets tab, click on 'Move or copy', select the master list file from the drop down list (master list file must be opened for it to appear) and click OK.

- Renaming the sheets and transposing the data to the master list sheet. A single transpose action will not be possible because of the merged cells.

Sub RunMe()
Dim ws As Worksheet
Dim x As Integer

x = 2

For Each ws In Worksheets
    If ws.Name <> "Master" Then
        ws.Select
        ws.Name = Range("B11").Value
        Sheets("Master").Range("A" & x).Value = Range("B4")
        Sheets("Master").Range("B" & x).Value = Range("B7")
        Sheets("Master").Range("C" & x).Value = Range("B9")
        'Finish this list for the rest of the data
    End If
    x = x + 1
Next ws
End Sub


- And finally print the entire file.

Can you work with that?

Best regards,
Trowa

0