Question

[smpaz7467]

I am a civil engineer. I use excel to sort out different areas, setbacks, and various information for each lot. I have all of this data on 1 sheet by lot number. I am wondering if there is a way to have all of this data be seperated into individual sheets. Ex. All of lot 1's information would create it own sheet automatically and copy all information related to lot 1 from my main sheet to the new sheet. And then it keep making sheets for the rest of the lots. Is this possible and if so what would I need to do?

 

[macajm]

I tried this with a macro using autofilter.
I had the lot numbers in ColumnA and other data adjacent in Sheet1.
The macro chose all data wil lotnumber="1" and copied it to a new sheet by itself.
This concept could be refined to add in manual input of a selected lotnumber or whatever.


Sub CopyDataToOwnSheet()
Range("A1").Select
Selection.CurrentRegion.Select
Selection.AutoFilter Field:=1, Criteria1:="1"
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=1

End Sub

 

[smpaz7467]

Ok, so my lot numbers start in A2 and go down from there. Then my data for each lot starts at B2 and ends in S2. And will this do this for each lot? Also will this name the new sheet to the correct lot number, say Lot 1?

 

[Norie]

You could use advanced filter for this.

Perhaps something like this.

Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long
    
    Set wsAll = Worksheets("All")
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    Set wsCrit = Worksheets.Add
    
    wsAll.Range("A1:A" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
    For I = 2 To LastRowCrit
    
        Set wsNew = Worksheets.Add
        wsNew.Name = wsCrit.Range("A2")
        wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A1"), Unique:=False
        wsCrit.Rows(2).Delete
        
    Next I
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub

Note this will copy the entire row, but can easily be adapted if you only want certain columns.

 

[smpaz7467]

You could use advanced filter for this.

Perhaps something like this.

Norie,

I am new to this type of coding, where would I place this in my worksheet? Also I forgot to mention that my data for each lot goes by house plan. So for example if the project has 4 different house plans, then lot 1 would start at A2 for plan 1 then A3 for plan 2 ect till all the plans for lot 1 were in the sheet. Would this effect the code?

 

[Norie]

This code would just go in a standard module.

You can create one by going to the VBA editor (ALT+F11), then Insert>Module, paste the code and use the Run menu to execute it.

The actual code I posted is generic code that will return all the unique items in column A.

It then goes through each of the unique items, creates a new sheet named after the item, then filters and copies data from the original tab based on the item.

Now that I've re-read your original post and read your last post I'm not 100% sure that's what you want/need.

Can you post some sample data and expected results?

 

[smpaz7467]

Now that I've re-read your original post and read your last post I'm not 100% sure that's what you want/need.

Can you post some sample data and expected results?

Yes I will post sample data tomorrow when I get into work. Is there a way to post the whole worksheet or is a sample the only way? If I could somehow get you a copy of the worksheet I could show you exactly what I am looking for. Thanks for the help, and I will post the information first thing in the morning.

 

[smpaz7467]

  A          B         C             D              E             F            
1 Lot Number Plan Name Left Driveway Right Driveway Front Setback Rear Setback 
2 1          5         LT            NCR            20            20           
3 1          6         LT            NCR            20            20           
4 2          5         LT            RT             20            20           
5 2          6         LT            RT             20            20           

Sheet 1
[Table-It] version 06 by Erik Van Geit

 

[smpaz7467]

  G             H            I              J        K             L        M              
1 Right Setback Left Setback Garage Setback Lot Area Building Area Coverage Building Width 
2 5             5                           6113     2950          0.48     40             
3 5             5                           6113     2634          0.43     40             
4 5             5                           6442     2950          0.46     40             
5 5             5                           6442     2634          0.41     40             

Sheet 1
[Table-It] version 06 by Erik Van Geit

 

[smpaz7467]

  N              O          P    Q        R             S              
1 Building Depth Lot Number EXFR Exposure Neighbor Left Neighbor Right 
2 80             9          NA   E        2             NA             
3 80             11         NA   E        2             NA             
4 80             8          0    E        3             1              
5 80             10         0    E        3             1              

Sheet 1
[Table-It] version 06 by Erik Van Geit