Report

Enter Data into main worksheet and have it update separate WS

Ask a question jamishore 10Posts Wednesday August 23, 2017Registration date September 13, 2017 Last seen - Last answered on Sep 7, 2017 at 10:42 AM by jamishore
I have a workbook that I would like to enter all my data into one main worksheet "2017 Job Cost Analysis" and have that data automatically populate all the other worksheets based on column A "Project Type".
Microwave
DAS
Fiber Interconnect
Inside Plant
Outside Plant
Small Cell plant
I have seen other posts on your forum that seem similar but everything I have tried does not work. Any help is greatly appreciated.

I entered this code but for some reason it is pulling columns A (Project Type) & B (Job Number and Name) to the correct tab, but the rest of the columns are just the top rows from the Main worksheet. Regardless of which project.

Sub TransferAllData()

Application.ScreenUpdating = False

Dim lRow As Long
Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
For Each cell In Range("A3:A" & lRow)
MySheet = cell.Value
cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Sheets(MySheet).Columns.AutoFit
Next cell

Columns(1).SpecialCells(4).EntireRow.Copy Sheets("Unassigned").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("Unassigned").Columns.AutoFit

MsgBox "Data transfer completed!", vbExclamation

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub


If I choose the project type, i would like the correct worksheet to be updated with the contents of that current row.
Helpful
+0
plus moins
Hello Jamishore,

Could you upload a sample of your work book please. It would be easier for us to determine how to best help you solve this if we had a sample to test with.

You can upload a sample to a free file sharing site such as ge.tt or Drop Box then post the link to your file back here. Please use dummy data and clearly explain inputs and expected results.

Thanks Jami.

Cheerio,
vcoolio.
Leave a comment
Helpful
+0
plus moins
I have uploaded my dummy file here:
https://www.dropbox.com/s/oc3gpqhglht6lty/2017%20JCA%20Report.xlsm?dl=0

What I would like to do is update the "2017 Job Cost Analysis" worksheet with jobs throughout the year and have it automatically update the Project Type worksheet, with the entire row for each project as it is added. I have a Project Type assigned to each in column A.

Also, I only want it to add new information, not duplicate (which it is doing now).

As you can see, for some reason, the project total section is not pulling over for the first 14 rows and I am sure it's because of my summary part that I have listed on the "2017 Job Cost Analysis" worksheet.

Any assistance is greatly appreciated!
Leave a comment
Helpful
+0
plus moins
Hello Jami,

Perhaps all you need is a Worksheet_Change event as follows:_


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
Target.EntireRow.Copy
Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Sheets(Target.Value).Columns.AutoFit

Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


This type of code needs to be placed into the worksheet module. So, to implement the code:-

- Right click on the 2017 Job Cost Analysis tab.
- Select "view code" from the menu that appears.
- Paste the above code in the big white field that then appears.

Go back to the main worksheet.

Place all your data in the required cells but make your last entry from the drop down selections that you have in Column A. As soon as you select a value from the drop down, the relevant row of data will be transferred to its respective sheet. Only values will be transferred over.

Following is the link to your file with the code implemented:-

http://ge.tt/9OEvKIm2

Make a selection from the drop downs in Column A to have the relevant row of data transferred to its individual sheet.

I hope that this helps.

Cheerio,
vcoolio.
jamishore 10Posts Wednesday August 23, 2017Registration date September 13, 2017 Last seen - Aug 25, 2017 at 11:05 AM
Thank you for looking into this. It seems kind of buggy because it is only working sometimes, and I came across another issue. If you need to delete or change something on the 2017 Job Cost Analysis Tab, it doesn't update the corresponding worksheet. So if I accidentally chose, DAS in column A, but meant to choose Microwave, it will update the DAS worksheet and will not undo it.
Reply
Leave a comment
Helpful
+0
plus moins
Hello Jami,

 It seems kind of buggy because it is only working sometimes

Buggy? Are you receiving any error messages? If so, which one(s)?

Working sometimes? I've just run the code (in the sample you supplied) over a dozen times repeatedly and it works seamlessly. Is your actual workbook a little different to the sample you supplied?

A few more questions:-

- Is the Range(A14:A39) from which the copying/pasting is done fixed? Based on the Project Type Summary at the top of the sheet, this is how I understand the data set to be formatted.
- Are the Project Types just the six that you have listed or will there be more?
- If they are fixed at six types, will the only cells to be changed be from the non-formulated columns in the column range B:AF?
- If they are fixed at six types, do you want the data that is transferred to the individual sheets to overwrite that which already exists in the individual sheets? This would mean that you would only ever have one row of data in the individual sheets. Or, do you need new data to append to the bottom of the existing data in the individual sheets.

I'll await your reply before going any further.

Cheerio,
vcoolio.
jamishore 10Posts Wednesday August 23, 2017Registration date September 13, 2017 Last seen - Aug 28, 2017 at 03:50 PM
Buggy? Are you receiving any error messages? If so, which one(s)? No error messages, just entered info and it didn't always bring it over to the correct worksheet. See the attached. I chose the project type last and nothing copied over.

Working sometimes? I've just run the code (in the sample you supplied) over a dozen times repeatedly and it works seamlessly. Is your actual workbook a little different to the sample you supplied? I updated the sample to include more formatting. The columns E; F; I; J; M; N; Q; R; U; V; Y' Z' AC; AD are all locked cells with a formula to calculate the 2 columns before each.

A few more questions:-

- Is the Range(A14:A39) from which the copying/pasting is done fixed? No it is not fixed from what I can tell. Based on the Project Type Summary at the top of the sheet, this is how I understand the data set to be formatted. The Project Type Summary is what I created the drop down list from.
- Are the Project Types just the six that you have listed or will there be more? There may be more.
- If they are fixed at six types, will the only cells to be changed be from the non-formulated columns in the column range B:AF? We really only need to bring over everything in that row besides the formulated columns listed above.
- If they are fixed at six types, do you want the data that is transferred to the individual sheets to overwrite that which already exists in the individual sheets? This would mean that you would only ever have one row of data in the individual sheets. Or, do you need new data to append to the bottom of the existing data in the individual sheets I would like it to append the data.

FYI on the worksheet, it is not password protected.
https://www.dropbox.com/s/35qr8603x1qswai/2017%20JCA%20Report.xlsm?dl=0
Reply
Leave a comment
Helpful
+0
plus moins
Hello Jami,

I've not tested the following as I'm short on time tonight, but, seeing that there are only certain columns to be populated in the individual sheets, try:-


Sub TransferData()

    Dim MySheet As String
    Dim c As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error Resume Next
For Each c In Sheet2.Range("A14:A39")
MySheet = c.Value
Range(Cells(c.Row, "A"), Cells(c.Row, "D")).Copy
    Sheets(MySheet).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Range(Cells(c.Row, "G"), Cells(c.Row, "H")).Copy
    Sheets(MySheet).Range("G" & Rows.Count).End(3)(2).PasteSpecial xlValues
Range(Cells(c.Row, "K"), Cells(c.Row, "L")).Copy
    Sheets(MySheet).Range("K" & Rows.Count).End(3)(2).PasteSpecial xlValues
Range(Cells(c.Row, "O"), Cells(c.Row, "P")).Copy
    Sheets(MySheet).Range("O" & Rows.Count).End(3)(2).PasteSpecial xlValues
Range(Cells(c.Row, "S"), Cells(c.Row, "T")).Copy
    Sheets(MySheet).Range("S" & Rows.Count).End(3)(2).PasteSpecial xlValues
Range(Cells(c.Row, "W"), Cells(c.Row, "X")).Copy
    Sheets(MySheet).Range("W" & Rows.Count).End(3)(2).PasteSpecial xlValues
Range(Cells(c.Row, "AA"), Cells(c.Row, "AB")).Copy
    Sheets(MySheet).Range("AA" & Rows.Count).End(3)(2).PasteSpecial xlValues
Range(Cells(c.Row, "AE"), Cells(c.Row, "AF")).Copy
    Sheets(MySheet).Range("AE" & Rows.Count).End(3)(2).PasteSpecial xlValues
Sheets(MySheet).Columns.AutoFit
Next c


Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False
Application.ScreenUpdating = True

Sheet2.Select
MsgBox "Done!", vbExclamation

End Sub


You will need to unprotect the main sheet and convert the "Microwave" sheet back to a normal range----> Go to the Design tab at the top of the sheet, over in the Tools group select "Convert to Range" (but remember to first click anywhere in the "Microwave" sheet data set).

Also, remove the existing code that is presently in the main sheet module.

As there are a mass of formulae in all sheets, the code will probably take approx. 4 - 5 seconds to execute (even though the code turns off automatic calculation which is turned on again at the end of the code).

Cheerio,
vcoolio.
jamishore 10Posts Wednesday August 23, 2017Registration date September 13, 2017 Last seen - Aug 29, 2017 at 01:57 PM
So to confirm, I would have to leave the main sheet unprotected?
Also, it does work, but I have to "Run" it each time and it pulls over the same data. So it will duplicate all data each time I "Run" it.

https://www.dropbox.com/s/5h3jy337pkaqzil/2017%20JCA%20Report.xlsm?dl=0

And I want to say I appreciate all your assistance on this.
Reply
Leave a comment
Helpful
+0
plus moins
Hello Jami,

Yes. For any code to work on any sheet, it must be unprotected.

However, to overcome the tedium of unprotecting/protecting the sheet each time you wish to use it, you can place the following line of code:-

Sheet2.Unprotect Password:="Whatever your password is"

at line 8 in the code above

and the following line of code:-

Sheet2.Protect Password:="Whatever your password is"


at line 38 in the code above.

If you don't have a password then just remove:-

Password:="Whatever your password is"

from both lines.

BTW, you have placed the code in the Worksheet module. Remove it from there and place it in a standard module and assign the code to a button.

The code is duplicating the data in the sample as that is the only data you have in the main sheet at present (nothing changed). I assume that you will be updating/changing the inputs daily. Duplicates can be removed if you find that static data is not changed regularly.

I hope that this helps.

Cheerio,
vcoolio.
Leave a comment
Helpful
+0
plus moins
I just wanted to get back to you and let you know that I am still playing around this workbook.

Thank you very much for your input it has been greatly appreciated.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!