Question

smpaz7467

Board Regular
Joined
Sep 18, 2006
Messages
77
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

macajm

New Member
Joined
Oct 21, 2005
Messages
32
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

Board Regular
Joined
Sep 18, 2006
Messages
77
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
You could use advanced filter for this.

Perhaps something like this.
Code:
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

Board Regular
Joined
Sep 18, 2006
Messages
77

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 18, 2006
Messages
77

ADVERTISEMENT

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

Board Regular
Joined
Sep 18, 2006
Messages
77
  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

Board Regular
Joined
Sep 18, 2006
Messages
77
  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

Board Regular
Joined
Sep 18, 2006
Messages
77
  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
 

Forum statistics

Threads
1,143,907
Messages
5,721,445
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top