Extract data from one sheet to multiple sheets based on sheet name/criteria

[Chiller75]

Hi,

I am using excel to track maintenance calls. Every call is entered into excel master sheet based on "Location, Task, Labor (00:00), Material Cost ($00.00), Material Description" there are several other cells but not needed for final output. At the end of the month, we take these values and enter them into individual owner statements. These owner statements are excel based and formatted with fields for "Task,Labor,Material,Description". Each workbook contains 65 sheets each formatted into an Owner Statement, Each sheet is identified by the location of the house. For example sheet "Lot #7" is specific to the owners and Lot #7 already has the owners name/address on it.

I am looking for some guidance on how to utilize the main data entry sheet to update the 65 owner statements based on criteria lot#, which in turn plugs the values associated with task,labor,material cost, material description into the specific owner statement cells associated with the master sheet location value. If there were multiple maintenance calls to a specific lot #, the data would plug directly below. The only way i can think of doing it using a massive amount of ifsums. there must be an easier way.

 

[My Aswer Is This]

You have given a lot of information here but no specific details.

I would think you should give us some specific details like this:

On my sheet named "Master" I have:

Customer account number in column "A"
Location in column "B"
Task in column "C"
Labor in column "D"

etc. etc.

And each sheet in my work book is given the name of the Lot number which is in Column "B"

Now when we want to update statements we need to take all the data in our master sheet and enter those values into our customer sheets

The values in column "A" of master sheet goes into cell "G2" of customer sheet. The value in column "B" of master sheet goes into cell "F2" of customer sheet.

And so on and so on.

Then we could have a Vba script copy all this data into the proper sheet and into the proper cell.

Now each customer sheet should be set up the same so on all sheets the same data goes into the same cell.

For example all sheets except master has the phone number for example in cell "B4" and address in "H5"

If you could provide details like this we maybe could help you.

 

[Chiller75]

You have given a lot of information here but no specific details.

I would think you should give us some specific details like this:

On my sheet named "Master" I have:

Customer account number in column "A"
Location in column "B"
Task in column "C"
Labor in column "D"

etc. etc.

And each sheet in my work book is given the name of the Lot number which is in Column "B"

Now when we want to update statements we need to take all the data in our master sheet and enter those values into our customer sheets

The values in column "A" of master sheet goes into cell "G2" of customer sheet. The value in column "B" of master sheet goes into cell "F2" of customer sheet.

And so on and so on.

Then we could have a Vba script copy all this data into the proper sheet and into the proper cell.

Now each customer sheet should be set up the same so on all sheets the same data goes into the same cell.

For example all sheets except master has the phone number for example in cell "B4" and address in "H5"

If you could provide details like this we maybe could help you.

Thank you so much for your help. I hope the following will provide the appropriate information;

Master Sheet
Column "a" - Location
Column "b" - Task Description
Column "c" - Labor (00:00)
Column "d" - Material Cost ($00.00)
Column "e" - Material Description

Owner Statement Sheets - (each sheet is named for the lot#, ex. Lot #7 , Lot #10 , Lot#200)
Cell B23 -Populate with Master Sheet Column B if condition is met that location from master sheet = location in Owner Statement Cell D3
Cell I23 - Populate with Master Sheet Column C if condition is met that location from master sheet = location in Owner Statement Cell D3
Cell M23 -Populate with Master Sheet Column D if condition is met that location from master sheet = location in Owner Statement Cell D3
Cell N23 -Populate with Master Sheet Column E if condition is met that location from master sheet = location in Owner Statement Cell D3

If Cells B23, I23, M23, N23 have populated, then Cells C24,I24,M24, N24 should populate until all matching criteria from Master sheet have been met.

I appreciate any help you can provide. Please let me know if i can provide additional details.

 

[My Aswer Is This]

So is Lot # and location the same?
So in "Master" sheet column "A" if it says "Lot14" then the data on this row should go into sheet named "Lot14"
Is that correct?
If not how do I know what rows data goes on what sheet?

 

[My Aswer Is This]

Assuming what I asked in last post is true try this:

And assuming you want to start coping data into first empty cells in column "B" "I" "M" "N" in your sheets try this.

It assumes your sheet names are in column "B' of the Sheet named "Master" starting in row(2)

Sub Test()
Application.ScreenUpdating = False
'Modified 7-31-17 4:45 PM EDT
Dim i As Long
Dim Lastrow As Long
Dim ans As String
On Error GoTo M
Sheets("Master").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
    ans = Cells(i, 1).Value
    Cells(i, 2).Copy Sheets(ans).Cells(Sheets(ans).Cells(Rows.Count, "B").End(xlUp).Row + 1, "B")
    Cells(i, 3).Copy Sheets(ans).Cells(Sheets(ans).Cells(Rows.Count, "I").End(xlUp).Row + 1, "I")
    Cells(i, 4).Copy Sheets(ans).Cells(Sheets(ans).Cells(Rows.Count, "M").End(xlUp).Row + 1, "M")
    Cells(i, 5).Copy Sheets(ans).Cells(Sheets(ans).Cells(Rows.Count, "N").End(xlUp).Row + 1, "N")
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You have no such sheet Name"
Application.ScreenUpdating = True
End Sub

 

[Chiller75]

So is Lot # and location the same?
So in "Master" sheet column "A" if it says "Lot14" then the data on this row should go into sheet named "Lot14"
Is that correct?
If not how do I know what rows data goes on what sheet?

So close, the macro ran without error, but the values didn't populate on the lot # sheets. I have the following sheets named;

Master, Verification, 2, 7, 10, 11, 12, 13, 14, 18, etc....

The hope is that when the code "scrubs" the Master sheet it populates the following information;

Lot # - cell B23 (Task)
Lot # - cell I23 (Labor 00:00)
Lot # - cell M23 (Material Cost ($0.00)
Lot # - cell N23 (Material Description)

Where as Lot # equals "Master" Column B and pastes values to sheet corresponding to "master" column B. Below is a screen shot of what i am trying to populate on the multiple lot# sheets

 

[My Aswer Is This]

I'm really not sure why I have a hard time understanding what people want.

I asked on the Master Sheet where do I find the sheet name this row of data goes to.

So now you said:Where as Lot # equals "Master" Column B

OK I can do that. But you earlier said column "B" had
"Column "b" - Task Description"

So I will tell the script to look in column "B" to find sheet name:

Here is a script that does that.
I do not want to know what the sheet names are.
I need to know where on the sheet named "Master" I can find the sheet name. And now your saying look in column "B" on sheet named "Master" for the sheet names

Sub Test()
Application.ScreenUpdating = False
'Modified 8-1-17 5:00 AM EDT
Dim i As Long
Dim Lastrow As Long
Dim ans As String
On Error GoTo M
Sheets("Master").Activate
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To Lastrow
    ans = Cells(i, 2).Value
    Cells(i, 2).Copy Sheets(ans).Cells(Sheets(ans).Cells(Rows.Count, "B").End(xlUp).Row + 1, "B")
    Cells(i, 3).Copy Sheets(ans).Cells(Sheets(ans).Cells(Rows.Count, "I").End(xlUp).Row + 1, "I")
    Cells(i, 4).Copy Sheets(ans).Cells(Sheets(ans).Cells(Rows.Count, "M").End(xlUp).Row + 1, "M")
    Cells(i, 5).Copy Sheets(ans).Cells(Sheets(ans).Cells(Rows.Count, "N").End(xlUp).Row + 1, "N")
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You have no such sheet Name"
Application.ScreenUpdating = True
End Sub

 

[Chiller75]

Thank you so much for your help. The script is working to pull data from master and paste into owner statements. My last question is how to get the data in the owner statements to paste to a specific cell as opposed to the first available. I have already added a macro to delete existing(old) data from the cells i want the new data to paste.

Essentially data from "master" should be pasted to corresponding lot# sheet into specific cells. For example data pulled from "master" should be plugged into the following cells based on condition that lot# matches sheet.

"Master" - if column B equals sheet name then paste data from column c, d, e,f into corresponding sheet "Lot #x " specific cells
c = B23
d = I23
e = J23
f = K23

 

[My Aswer Is This]

I guess we will get this straight some time:
In Post 6 you said:

Lot # - cell B23 (Task)
Lot # - cell I23 (Labor 00:00)
Lot # - cell M23 (Material Cost ($0.00)
Lot # - cell N23 (Material Description)

B
I
M
N

Now your saying:
B
I
J
K

Try this:

Sub Test()
Application.ScreenUpdating = False
'Modified 8-1-17 5:00 AM EDT
Dim i As Long
Dim Lastrow As Long
Dim ans As String
On Error GoTo M
Sheets("Master").Activate
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To Lastrow
    ans = Cells(i, 2).Value
    Cells(i, 3).Copy Sheets(ans).Cells(23, "B")
    Cells(i, 4).Copy Sheets(ans).Cells(23, "I")
    Cells(i, 5).Copy Sheets(ans).Cells(23, "J")
    Cells(i, 6).Copy Sheets(ans).Cells(23, "K")
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You have no such sheet Name"
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

This may be more easier to read:

Sub Test()
Application.ScreenUpdating = False
'Modified 8-1-17 5:00 AM EDT
Dim i As Long
Dim Lastrow As Long
Dim ans As String
On Error GoTo M
Sheets("Master").Activate
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To Lastrow
    ans = Cells(i, "B").Value
    Cells(i, "C").Copy Sheets(ans).Cells(23, "B")
    Cells(i, "D").Copy Sheets(ans).Cells(23, "I")
    Cells(i, "E").Copy Sheets(ans).Cells(23, "J")
    Cells(i, "F").Copy Sheets(ans).Cells(23, "K")
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You have no such sheet Name"
Application.ScreenUpdating = True
End Sub

You should be able to read the script and see:

Column "B" on Master is sheet name;
Columns "C" date goes to column "B"
Column "D" data goes to column "I"

Etc.
Etc.

Modify code if needed.