Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VB Question

Posted on 2015-01-06
7
Medium Priority
?
60 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 49

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 49

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

610 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