Solved

Excel VB Question

Posted on 2015-01-06
7
52 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 45

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 45

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

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 45

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now