Solved

Excel VB Question

Posted on 2015-01-06
7
54 Views
Last Modified: 2015-01-12
I have an excel work schedule, tab 2015, that shows my teams vacation availability through out the year.  I only publish 8 weeks of the schedule at time and this published to  sharepoint web site.  In order to avoid the cleaning the publishing sheet and the coping and pasting the current 8 weeks I created a script to do this for me.  Now the script works well but I have experienced some issues that I need help with.  I'm just learning VB and have figured some things out on my own and gotten some help, so if you see something that I have done wrong or feel it can be done more efficiently please feel free to make suggestions.  

 Here are my issues that I need help with.  First though you need to know that the first 3 rows are always the header rows.  These don't change.    
 1.  My script hides all of the data, except the current 8 weeks.  For example, if the current week is Feb 1, 2015, using the 2015 tab, it will hide rows 4 though 71 and then hide rows 207 to 885.  I want it to hide all of the calendar data prior to the current week, as my team and I don't need to see that each time and it allows me to quickly make changes to the calendar on a go forward basis, but I do not want it hide the rest of the calendar.  I got some help with this part of the script and figured I could get the rest to work using a row count and the unhide command to accomplish it but haven't been successful.
 2.  My row heights aren't consistent.  So each week of the calendar has a day row, for the days of the week, and the date row, for the date.  These rows are formatted at 13.2.  Then the Employee part f the schedule is formatted at 26.4.  For some reason during the copy and paste part of my script my row heights change.  So I tried to write a script that did a row count and then changed the row height to 26.4.  Again, I was unsuccessful.  

 I have included the file for review
ShiftSchedule1.xlsm
0
Comment
Question by:Rrave26
  • 4
  • 2
7 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40535934
Do you want the row heights and column widths of the SP Publish sheet to wind up the same as they are on the 2015 sheet?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40536190
I went ahead and assumed that the answer to my question was "yes", so try this.
Q-28591479.xlsm
0
 

Author Comment

by:Rrave26
ID: 40544655
Thanks Martin for the help.  Well the new code definitely fixes the issue with un hiding the rest of the calendar, in this case the 2015 tab.  Thanks.
Because I was out of pocket and unable to answer your question I know that you set the row height to match the 2015 sheet.  However, I noticed when I ran the script that there were several rows in the SP Publish sheet, where the row height wasn't consistent.  For example when I ran the code and looked at this week in the SP Sheet, I see that rows 5 and 6 are set at 27, Row 17 is set at 26.4 and the rest of the rows are set at 13.8.  I would like to set all of the rows that have employee names in it to 26.4.  The rows that contain the day of the week and the date can be kept at 12.6.  I also noticed that my dates no longer show up in the SP Publish tab.  I looked at the code and I don't know why this is happening.

In Module 1 you asked:
strYear = Format(Date, "yyyy")
lastSunday = Date - (Date - 1) Mod 7
Set oSht = Sheets(strYear)

' Why do you do this?
'oSht.Range("A4:A10000").EntireRow.Hidden = False
I wish I can answer this, I got help with calculating the date and when I asked for an explanation on what this did I didn't get a reply,  Sorry.

I also see you added a 2nd module and went through the code to try and understand it but it looks like you are just selecting rows copying and pasting them into sheet 3.  Is there a reason for this?
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40544867
The attached corrects the row sizing and the dates.
I also see you added a 2nd module

If you are talking about Module2 then if i added anything there it was for testing and it wasn't needed so I removed that module.
Q-28591479a.xlsm
0
 

Author Closing Comment

by:Rrave26
ID: 40544883
Thank you for your help.  One last question for you, can explain with the zoom code I actually doing?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40544981
Zoom, which is available on the 'View' tab allows a sheet to be made smaller or larger. I noticed that the Zoom level was different between the two sheets so I made them the same.

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question