Auto populate sheets based on main spreadsheet

Closed
amy2876 Posts 5 Registration date Friday March 28, 2014 Status Member Last seen April 2, 2014 - Mar 28, 2014 at 12:08 PM
amy2876 Posts 5 Registration date Friday March 28, 2014 Status Member Last seen April 2, 2014 - Apr 2, 2014 at 09:34 AM
Hello,
I am having some trouble creating a building floor directory. I would like to have one main spreadsheet with everyone's name, title, department, floor and phone number and then separate the names to subsequent sheets based on what floor they are on and then name those sheets by floor number as well. Any help would be greatly appreciated.
Thanks in advance for your help.
Related:

8 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 29, 2014 at 01:16 AM
without some extract of data it is difficult to find what you want.

you park the floor number in a separate column and then sort according to this collumn
0
amy2876 Posts 5 Registration date Friday March 28, 2014 Status Member Last seen April 2, 2014
Mar 31, 2014 at 11:59 AM
The title headings are: NAME, TITLE, DEPARTMENT, ROOM, FLOOR, PHONE

I would like a new sheet in the workbook created for each floor, with rows populated with the information from the listing of the entire building on the main sheet. This way, I can always update the main sheet and have individual sheets printed for each floor when needed.

It seems like it should be really simple to do but I have tried with no success. I've even watched Youtube videos on the subject but can't quite find what I am looking for.

Thank you.
0
amy2876 Posts 5 Registration date Friday March 28, 2014 Status Member Last seen April 2, 2014
Mar 31, 2014 at 12:31 PM
I should also add that the reason I do not so a simple "Filter" settings on this is because I need to create different print layouts on each spreadsheet. I would like to just pull the info in and hit print without have to re-do all my print settings.
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 1, 2014 at 02:02 AM
I requested you to post A SMALL EXTRACT OF DATA. with that data please again explain what you want. if can give sheet containing what you want will also be usefl
0

Didn't find the answer you are looking for?

Ask a question
amy2876 Posts 5 Registration date Friday March 28, 2014 Status Member Last seen April 2, 2014
Apr 1, 2014 at 04:37 PM
Yikes, so sorry. No need to bark. Here is the extract....

NAME TITLE DEPARTMENT ROOM FLOOR PHONE
Cazares, Oscar Chief Clerk Academic Affairs C-118 1 x 5882
Hanna, Marianne Office Support Specialist Academic Affairs C-102 1 x 5426
Helldobler, Richard Provost/Vice President Academic Affairs C-112 1 x 5420
Lopez, G. Yvette Executive Assistant Office of the President C-209A 2 x 5402
Perez, Suleyma Executive Director Government Relations C-210 2 x 5415
Puentes, Julio Regional Director USDA Office of the President C-220 2 x 4235
Demma, Jason Visual and Creative Specialist Communications and Student Media C-328 3 x 4747
Duetsch, Craig Assistant Vice President for Business Services Business Services C-317 3 x 5144
Filipp, Robert Assistant Vice President for Procurement and Support Services Procurement and Support Services C-309 3 x 5308
Lopez, Daniel Associate Vice President Student Affairs C-322 3 x 4600
Neumeister, Tasha Director Communications and Student Media C-327 3 x 4528
Pierick, Michael Vice President for Finance and Administration Finance and Administration C-318 3 x 5100
Peters, Victor Assistant Director Office of University Budgets C-425 4 x 5113
Rodriguez, Maria Budget Analyst II Office of University Budgets C-424 4 x 5116


Again, I would like a new worksheet created for each floor, with rows populated with all the information from the original worksheet.

Does that help? Not sure I can try to explain that any differently.
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 2, 2014 at 12:40 AM
I am NOT BARKING. that is only for emphasis. I shall look into it. as this is voluntary work in addition to my own work it may take little time

await next message. thanks
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 2, 2014 at 01:32 AM
I am sending the workbook. download the file from this url

http://speedy.sh/j4AUE/amy-140402.xlsm


first run the macro "undo"
and then
run macro "test"

is this what you want.

appears to be some error in President row (row no.5. I made the room no. 2
if this mistake correct it but give some room number

2 macros test , undo and one function. the macro test utilizes the Function.These are in the standard module of vbeditor of this file.

they are repeated here for others sake

Sub test()
Dim Floor As Range, rdata As Range, unq As Range, cunq As Range, FloorNr As Variant
Application.ScreenUpdating = False
Worksheets("sheet1").Activate
Set Floor = Range(Range("E2"), Range("E2").End(xlDown))
Set rdata = Range("A1").CurrentRegion
Set unq = Range("A1").End(xlDown).Offset(5, 0)
Floor.AdvancedFilter xlFilterCopy, , unq, True
Set unq = Range(unq.Offset(1, 0), Cells(Rows.Count, "A").End(xlUp))
For Each cunq In unq
FloorNr = cunq
rdata.AutoFilter field:=Range("E1").Column, Criteria1:=FloorNr
rdata.SpecialCells(xlCellTypeVisible).Copy

If Not sheetexists(FloorNr) Then
Worksheets.Add
ActiveSheet.Name = FloorNr
'GoTo copying
'Else
'GoTo copying
End If
copying:
With Worksheets(FloorNr)
.Range("A1").PasteSpecial
End With
Worksheets("Sheet1").Activate

ActiveSheet.AutoFilterMode = False
Next cunq
Range(Range("A1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete

MsgBox "macro done"
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Function sheetexists(n As Variant) As Boolean
Dim ws As Worksheet
sheetexists = False
For Each ws In Worksheets
If n = ws.Name Then
sheetexists = True
Exit Function
End If
Next ws
End Function

Sub undo()
Dim j As Integer
Application.DisplayAlerts = False
For j = Worksheets.Count To 1 Step -1
If Worksheets(j).Name <> "Sheet1" Then
Worksheets(j).Delete
End If
Next j
MsgBox "undone"
Application.DisplayAlerts = True
End Sub
0
amy2876 Posts 5 Registration date Friday March 28, 2014 Status Member Last seen April 2, 2014
Apr 2, 2014 at 09:34 AM
Hi there. Thank you so much for your work on this. I am unable to download the file. The file has been flagged by the antivirus software here at work. I will try to copy and paste the code into the document manually and let you know how it goes.

Thanks again. I appreciate your help.
0