Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel VB Question

Posted on 2015-01-06
7
Medium Priority
?
63 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
6 Comments
 
LVL 50

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 50

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 50

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 50

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

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.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 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