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.

 

[Chiller75]

The code is doing everything we want it to do. It is pulling data from master sheet to the various columns for the owner statements. The trouble i am having now is it is not pulling all the data.

Master Sheet - Column B - has multiple entries for each each work order based on lot#. It is pulling the data from some of the rows but not all. when it post to owner statements for lot 2,7, etc... it only pulls the data from the bottom entry and disregards the rest of rows for that lot # rows. How do i get the code to consider every row and pull from all based on lot# so multiple tickets can be posted. Below is how the lot master sheet looks before pulling to owner statements.
[TABLE="width: 761"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]07/31[/TD]
[TD]2[/TD]
[TD]Assemble dining rm chair[/TD]
[TD]0:15[/TD]
[TD] $ 2.00[/TD]
[TD]screws[/TD]
[/TR]
[TR]
[TD]07/26[/TD]
[TD]2[/TD]
[TD]tub drains slow[/TD]
[TD]00:30[/TD]
[TD] $ 9.89[/TD]
[TD]Tub stopper[/TD]
[/TR]
[TR]
[TD]07/03[/TD]
[TD]7[/TD]
[TD]Fix privacy fence in back yard. Has been approved[/TD]
[TD]03:41[/TD]
[TD] $ 103.44[/TD]
[TD]Three 4x4x10 pt lumber
6 bags of sakrete
1 box of screws[/TD]
[/TR]
[TR]
[TD]07/19[/TD]
[TD]7[/TD]
[TD]repair blinds in master[/TD]
[TD]00:20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/23[/TD]
[TD]7[/TD]
[TD]Dryer vent tube under house needs cleaned out[/TD]
[TD]00:20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

I believe the problem is you said in your last post to always enter the data into cell(23) so each time it finds another entry with that sheet name it copies it into row 23. Over writing what is in row 23




In your first posting you said if row 23 is filled then post in row 24 and if cell 24 is filled post in 25
But then later in your postings you said always post to cell 23

From post 3
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.
From Post #8
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.


So what is it always post to 23 or first empty cell in column:

We cannot have it both ways:

 

[Chiller75]

Sorry for the wishy washy, the document has been evolving.

The owner statements contain various other cells filled with information pre loaded with owners name address, type of home, etc.... the portion that tallies the monthly work orders for the property start on row 23 and in theory can go on to 100. I have only seen 10 max. Ideally the work orders start populating at row 23 and keep populating until all rows with corresponding lot#'s populate the owner statement sheets. Since his workbook will be used monthly, i added a macro to delete all data from B:23 to N:40 to start off with a clean sheet but preserve the proprietary data.

in a nutshell, looking to start populating at b23 and continue until all rows with corresponding lot# are satisfied.

 

[My Aswer Is This]

I'm going to have to think about this some more.

Your saying you want to start entering data in row 23 but that rows 1 to 22 may have values and may not have values.

So I cannot tell the script to start filling in when it finds last row with data.

I will get back with you.

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
'Modified 8-1-17 5:00 AM EDT
Dim i As Long
'Modified 8-3-17 6:05 PM EDT
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
    
    Lastrowa = Sheets(ans).Application.Max(22, Sheets(ans).Range("B" & Rows.Count).End(xlUp).Row) + 1
    Cells(i, "C").Copy Sheets(ans).Cells(Lastrowa, "B")
    Cells(i, "D").Copy Sheets(ans).Cells(Lastrowa, "I")
    Cells(i, "E").Copy Sheets(ans).Cells(Lastrowa, "J")
    Cells(i, "F").Copy Sheets(ans).Cells(Lastrowa, "K")
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You have no such sheet Name"
Application.ScreenUpdating = True
End Sub