Solved

Excel VB Question

Posted on 2015-01-06
7
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 47

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 47

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 47

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 47

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

729 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