Hiding Rows if cell is blank

Attached is a workbook that I have received help with. When you press the Create New Sheets button on the Data tab, the Main Sheet worksheet will be copied for each Department Name/#, and the full name of all the Employees as well as their Employee ID will populate in columns A and B. Columns A and B are blank from row 3-100 for names to be populated in.

I want to hide all rows from 3-100 that are blank. I believe the code can say, if rows 3-100 are blank in column A, then hide the row. I need this done for each department worskheet that gets created from clicking the Create New Worksheet button.

Does this help?

Please Login or Register  to view this content.

I inserted the second line of code but that only hides the rows for the last worksheet that gets created. I would need this code to work on all of the department worksheets that gets created.

Maybe:

Please Login or Register  to view this content.

There is an error that comes up. Check out the attached.

You need to add your SheetExists Function to a module.

I am not sure what that means. Are you able to tweak the workbook I attached in my last message so that it works. That would be helpful for me. Thank you.

This function was included in your Post#1 attachment. I don't know how it got deleted but it is not there in your Post#5 attachment.

Please Login or Register  to view this content.

It looks like we are close. Look at the attached. The coding is hiding all rows 3-100. The Employee ID and names are now not populating on each worksheet.

See if this works for you.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The cashier department should have 12 Employee ID's and names in the worksheet that gets created. I pasted your code into the document. Press the "Create New Sheets" button on the data tab. You will see that every row is being hidden and the Employee IDs and Names are not populating correctly.

On my test the rows 3-100 are hidden for each new sheet?

This is great. I tried giving you more reputation points but it won't let me add anymore.

Hey one more question that I came across. When I make copies of the worksheet, the graphs that get created still reference the "Main Worksheet" tab for the Horizontal access which is where the names are suppose to be. How do I change the horizontal access formula on the Main Sheet so that the formula references the current worksheets names? I attached the workbook.

If you click the Create New Worksheets button and then look at the horizontal access in one of the graphs, you will see what I mean.

I think the horizontal cell reference may include the following but I can't get it to work


="'"&MID(A2,FIND("]",A2)+1,256)&"'!"

John I wrote to daffodil11 about this but maybe you can help find a solution. When the Main Sheet is copied for each department, the graphs seen in row 101 and below have a horizontal axis reference to the Main Sheet versus the current department sheet. Is there something in the VBA code or the horizontal access formula on the graphs that we can change so that each worksheet that gets created can reference that worksheet's horizontal access data.

Let me know if this makes sense. I have attached the worksheet.

daffodil11 I reached out to John and I may have messaged you yesterday but I do not remember. When the Main Sheet duplicates for each department, the cell references for the graphs starting in row 101 still refer to the Main Sheet. Is it possible to tweak the VBA code so the cell references refer to the current worksheet or maybe change the formula in the references?

Let me know your thoughts. This is my last task before I can roll this out to my employees.

Amazing! This has been so helpful. The tool that you have helped me build is used for scheduling. Now that all of the all the tabs built are for each department and show all the employees, we can schedule each employee for the week. I need to now be able to load the data into the scheduling tool we use.

I created a data load tab. On this tab, I have three columns. Date, Employee ID, and Time. I need to take all of the data from each department tab that got created and then take all the Employee IDs, the time that they get scheduled for each day, and the date for each day. I would assume that the formula would say take all the Employee IDs and the times scheduled and place that information one right under the other on the Data Load tab as well as populate the date which is essentially a column in each department tab.

Does this make sense. Once this is done I can upload all data.

If it is easier, we can take all data from rows 3-100, which will contain mostly blank rows, and have that copy and pasted underneath one another on the data load tab. As long as the Employee ID, Date, and scheduling time shows, I can then filter the data to exclude any blank rows. This may make the coding easier.

I only need the three fields seen in the Data Load tab which are Date, Employee ID, and Time. Time is just the format that we book such as "0200-0800".

This can be an entirely different Macro since I will first push the Create New Sheets Button I created and then from there I need to populate all the scheduling details for each employee in the departments. Once that is done. I need all the EmployeeIDs, Dates, and Scheduling Times to be all on one sheet so I can load it into my system. I do not care which department the employee is in since an employee is only seen in one department which is stored in our system.

It looks to be working until Employee ID 6391 in row 849. Any idea why the code would stop working once it hit that ID? I cant attach the file since it is too big to attach on this forum.

When I run the Create New Worksheet Macro, I get the following message: "Run Time Error 1004 Invalid Paramter". The following line of code is highlighted. Do you know why this would occur?

ActiveChart.SeriesCollection(1).XValues = "='" & Sheets(x).Name & "'!$AC$3:$AC$100"

On the Main Sheet tab, if there is nothing filled in for Employee ID and Employee Name, then the error comes up. If you actually have two random words in cells A3 and B3 on the Main Sheet, then there is no error.

Daffodil11. I think I have figured it out. If an Employee does not have a time entered in for all the days of the week, the code breaks and then does not fill down on the data load tab. This means that every employee needs to have times filled in or the program won't work. I can live with that if there is not an easy solution.

The one main thing I have noticed is that on the Main Sheet, cells A3 and A4 need to have something filled in otherwise the New Sheets do not get created appropriately. Is this something you can fix? Basically if there are NO employee ID or names filled in on the Main Worksheet, the macro doesn't run appropriately.

Thank you for all your help. I am impressed with your skills.

I think we are fine on that one. If there is an employee that we do not want to schedule, I can just delete that row from the department sheet. It looks like every employee needs to have data filled in for the macro to work. So we are good with this.

The only thing at this moment I see is that the Main Sheet needs to have text written in A3 and B3 otherwise when you duplicate the sheets, all the rows are hidden. Is there a way for the Create New Sheets to work without having to have dummy data in the A3 and B3 in the Main Sheet tab?

Daffodil11. Is there a quick fix to the create new sheet code that will allow us to copy the Main Sheet without having to have cell A3 and B3 filled in. An error comes up if we leave A3 and B3 blank on the Main Sheet.

Thanks again. You have showed me a ton of things that are very interesting.

Everything else works great but there must be something in the code that doesn't allow A3 and B3 to be blank on the Main Sheet

Below is what gets highlighted as being an error in the code


ActiveChart.SeriesCollection(1).XValues = "='" & Sheets(x).Name & "'!$AC$3:$AC$100"

Daffodil11. I have a quick question that I think will be easy for you to do. If I create a worksheet called Department and want to list 20 department names in cells A1 to A20, I want the Create New Sheets tab to only create departments that fall into that list. That means if the data tab has a department and department number on there but does not show up on this list, then I don't want the department to have its own worksheet.

Let me know if this makes sense.

Adam

Daffodil11. A possible solution is to just get rid of the "read column G for unique departments" for duplicating sheets and to just read the department worksheet. I want to have two versions of this workbook. The one we have and then one where the Department Name/# has to be written in A1-A20 of the Department worksheet. I still want all the Employee Names and IDs to be captured as well for each department. Let me know if you are able to do this. Thanks!

Sub CopySheets()

Dim wsData As Worksheet: Set wsData = Worksheets("Data")
Dim wsMain As Worksheet: Set wsMain = Worksheets("Main Sheet")
Dim ws As Worksheet
Dim myChart As ChartObject
Dim x As Long
Dim strName As String
Dim arrDepts
Dim arrTemp

arrTemp = wsData.Range("G2", wsData.Cells(Rows.Count, "G").End(xlUp))
Application.ScreenUpdating = False
'Create array of possible sheet names
With CreateObject("Scripting.Dictionary")
For x = LBound(arrTemp) To UBound(arrTemp)
If arrTemp(x, 1) <> "" And Not .Exists(arrTemp(x, 1)) Then
.Add arrTemp(x, 1), Nothing
End If
Next x
ReDim arrDepts(1 To .Count, 1 To 1)
arrDepts = .keys()
End With
'Create sheets of unique names
For x = LBound(arrDepts) To UBound(arrDepts)
strName = arrDepts(x)
If Not SheetExists(strName) Then
wsMain.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = arrDepts(x)
End If
Next x
'Populate each sheet with respective data
For x = 2 To UBound(arrTemp)
If wsData.Cells(x, 1) <> "" Then
Sheets("" & wsData.Cells(x, 7) & "").Range("A1").End(xlDown).Offset(1) = wsData.Cells(x, 1)
Sheets("" & wsData.Cells(x, 7) & "").Range("A1").End(xlDown).Offset(0, 1) = wsData.Cells(x, 8)
End If
Next
'Hide unpopulated rows
For x = 3 To Sheets.Count
On Error Resume Next
Sheets(x).Range("A3:A100").SpecialCells(4).EntireRow.Hidden = True
If Sheets(x).ChartObjects.Count > 0 Then
For Each myChart In Sheets(x).ChartObjects
myChart.Activate
ActiveChart.SeriesCollection(1).XValues = "='" & Sheets(x).Name & "'!$AC$3:$AC$100"
Next myChart
End If
Next
Worksheets("Data").Activate
Application.ScreenUpdating = True

End Sub
Function SheetExists(SheetName As String) As Boolean
On Error Resume Next
SheetExists = InStr(1, Sheets(SheetName).Name, SheetName, vbTextCompare)
End Function

Yes I would want the Employee IDs and Names to still populate. The steps would be as follows:

Look at the Department Tab and create a worksheet for those departments. Then populate the Employee ID and Employee Name for those departments in each worksheet like you already have. I think the only difference in the code is rather than look at column G in the data tab for unique departments, look in the Department tab.

In the attached, I have included the 3 departments to the Department tab that are seen in the Data tab. The reason why I want this is because I do not want this spreadsheet sent to other people in my company so they can use it for their division. By having to type in the department names in the Department tab, I can lock that tab and allow only certain people to use it for their division. Make sense?

Code would be create duplicates of the Main Sheet by using the names in the Department tab. From there, populate each department tab with Employee ID and Name from the data tab. Second part of this should already be done I believe from your formula.

Daffodil11. I think I am very close. In the workbook attached, I added the following code. This code works if all of the Department names that are on the Data sheet are seen on the Departments tab. If I exclude one of the three departments on the Departments tab, the code breaks at the step 'populate each sheet with respective data' and does not populate each tab with the Empployee ID and Employee Name. I would like the code to work so that I can type in which departments I want in the Department worksheet. Obviously these departments need to be seen on the data sheet. I believe it is a simple solution.

Please Login or Register  to view this content.

This is great. I am learning a lot. I took out two columns in the data sheet in the attached. I adjusted the code so that everything would read appropriately on each department worksheet. For some reason, the very last person on the data sheet never shows up on the department worksheet that he/she is in. Do you know why?

Esther Kribbs does not show up in the produce worksheet. If I type a name under her and then a department, she will show up but then that new name won't show up

I changed the following line by changing C2 to C1 and it now works.

arrTemp = wsData.Range("C2", wsData.Cells(Rows.Count, "C").End(xlUp))

daffodil11

This seems to be an issue I had from the beginning that I was not able to fix or address. If you look at the attached, I inputted time slots for each Employee on Sunday and then one employee for Monday. When I ran the DataLoad macro, the information did not move correctly to the Data Load sheet. If you look at the data load sheet, you will see that all time slots went to EmployeeID 0669760. I think the only time the data load worksheet works is if every single time slot for every single person in every department is filled out.

Are you able to adjust the VBA code so that not every time slot needs to be filled in for every employee?

In addition the bottom of the data load sheet has a bunch of departments listed down it randomly. If you can help fix this you would be a lifesaveer. This is almost working perfectly!!!!

A simple fix is all that was needed. Amazing!

Every Department Name has one department number which can be seen in the Data tab. In the data load tab, I want to have column D1 say Dept#. Can we add one line of code that will add the Dept# in column D to the Data Load tab.

There may be a case where an employee works in two departments. With that said, we cant have a lookup created based on employee ID but rather, each department worksheet can be coded to a Department # by doing a lookup on the data tab. Does this make sense? For example, all data that get populated on the Data Load tab from the 482BakeryDeli worksheet will have 482004 a the Department# in column D. All data that get put onto the data tab from 482 CashierBooth will have 482006 populated in column D on the dataload tab.

These codes are perfect. When it comes to printing, I know what I want but how do I format each page that gets created to do the following:

- Capture cells A1-J100. I am assuming this will capture all employees on each sheet and then the hidden rows will just remain hidden.
- In the header, have the sheet name in 20 font. In the footer have on the bottom right the current date in size 14 font.
- Print landscape and fit to one page.