Enter Data into main worksheet and have it update separate WSClosed

Question

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.

vcoolio · Answer

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.

jamishore · Answer

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!

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

jamishore · Answer

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.

Enter Data into main worksheet and have it update separate WS

7 responses

Similar discussions

Subscribe To Our Newsletter!