Posts by nm766

    I have a Workbook contains more than 30 worksheets of Which, in 26 Sheets the Range used is same i.e. A1 : I83. This is formatted in to two pages each for printig. Now, I want to get Range(A1:I83) from all 26 sheets in to a single sheet (Say Named "Master Print" with same formatting and values so that I would be able to give a print command for all the 26x2=52 pages in one go. I need not to go to each sheet for printing those two pages which will make me to give print command for 26 times. in the "Master Print" Sheet , if Sheet 1 data covers from A1:I83, Sheet 2 data should cover from A84 to I167 and so on. Please. This should be done from a User form Command button Say "Prepare Print Page" . Secondly, the remaining Sheets other than these 26+1 Sheet should not be affected. Kindly Help.

    Thanks & Regards. nm766

    All my dear friends,
    In a Work book, I created an User form having data in 15 text boxes that needs to be transferred to 15 selected cells (in each sheet) in 30 sheets (differently named) simultaneously. I mean, in each sheet, there are fifteen different cells meant for the 15 text box data of the userform. For example, If textbox 1 data is meant for Cell No G6 it should go to cell G6 in all the 30 sheets and while saving the data, this should save in one go for all the 30 sheets. I mean, in one click on the save button on the userform, it should save the data in defined cells in all the 30 sheets simultaneously.

    Dear friends,
    I am in a fix to sort out a problem asmentioned below and in need of help to find out a solution.
    In a sheet, I need to write Starttime and Finish time in two Consecutive ranges say Start times in Range A & Finish times in Range B; both columnsformatted in time format (37:30:55)
    Now, in each Row, time in Range B cells will always be higher than its corresponding cells in Range A. For example, time data in B1 will be always higher than time data in A1. Sothere will be positive time value for B1-A1.
    Now, I want to highlight the cells of Range B i.e. I want to highlight B1 if the difference (B1 – A1) is greater than 15 minutes (00:15:00) and similarlyin cases of B2-A2, B3-A3 and so on.
    I am unable to put the formula for applying the conditional formatting. Can it be done through conditional formatting or excel VBA. Please help me to resolve the issue.
    I will be ever grateful to youall.
    Awaiting an early response.

    Dear Ozgrid Friends,
    Thanks for the cooperation extended sofar. I am in need of help from you.

    Problem 1: (a) In a User Form, I have three Text Boxes named Date of Birth (TxbDOB), Date of Joining (TxbJoin) and Date of Exam(TxbExam). As on the Date of Exam, The Age will be [(TxbExam –TxbDOB)/365] and the result I need on a TextBox Named TxbAge on the same user form which will be only the Years like 32, 44 etc and not the fraction part.

    Similarly, The Length of Service may be calculated as [(TxbExam – TxbJoin)/365] and the result should be displayed in a Text box named TxbService in the same User form in round figure in years like the Age.

    I am unable to compose it in VBA. Kindly help me so that while transferring the data to the worksheet from the user form, direct result ie. Age and Length of Service in years only could be saved along with other data. It must be noted that, the result should come intantly after filling up the text boxes i.e.
    Date of Birth (TxbDOB), Date of Joining (TxbJoin) and Date of Exam(TxbExam). The sequence /Tab order for these Text Boxes are Date of Exam(TxbExam), Date of Birth (TxbDOB) and Date of Joining (TxbJoin). My requirement is after filling up the Text box - Date of Joining, The age and the Length of Service should be calculated and displayed in their respective text boxes in round figures.

    I will be eagerly waiting for your help.

    Thanks and Regards,


    Re: Filtering monthwise from multiple Data sheets and pasting in respective month sh

    Dear Jindon,
    Thanks for looking in to my problem. In recent past your help has extremely assisted in resolving some issues which were beyond my level. I would once again thank you for your valuable support. For this problem, I have reattached the sample sheet. I have worked out the feeding of data on the user form and getting them stored Category wise. Also To edit them by bringing back the recorded data on to another user form as edit form. But getting them transferred to month wise sheets has become the problem. requirement is leave for the month of January in each Category sheets ( Total 11 Sheets), the same rows should come to Sheet Leave_January and so on for other months as well. Hope you have under stood the problem. Once again thanks for your help so far and awaiting for a solution.

    Dear Friends,
    Can anyone help me out from this problem? First of all I must pay my regards to all members of Ozgrid family for their co-operation extended to me so far. Now I am suck in a problem where I need help to resolve the issue.
    Problem :
    1. I have a Work book to maintain leave record of persons of a company. From Sheet No 3 to 13, I store the leave data in Columns B to Col. AA. Column A is for Sl no. I have 11 types of groups of persons and from a user form, I store the data group wise in these Sheets 3 to 13. Now I want to store the data month wise i.e. I want to use 12 more sheets for 12 months to store the data in same pattern month wise. This requires to filter data month wise from the 12 group wise sheets (Sheet 3 to 13) and paste them in 12 sheets designated for months(Jan, Feb……up to Dec). This should happen when I click a Command button on the user form say “Update Monthly data”. While updating monthly sheets, the group wise monthly data from the 13 sheets should get appended sheet wise i.e. first sheet 3 then sheet 4 and so on up to sheet 13 in the respective month sheets( Jan…Feb……Dec)

    2. It may be note that at the beginning of the year i.e. January, leave data will be generally available for approximately next 4 months i.e. may be up to April or so as people are allowed to plan and apply for leave on dates within next 4 months and it gradually covers the other months as days goes on. Hence while transferring data to month wise sheets; NIL data may be available for the rear months.

    I am attaching a sample sheet for better understanding.

    Thanking you all in advance.
    Request an early reply.

    Dear Friends,
    Once again, I need your help for certain issue I am facing .
    I have a Work book with few sheets. From a User form I click a “Update” button to save the data fed on to the user form. In the user form, One text box is used to enter date i.e. the From Date and one Text box is used to indicate number of prefixes with the date. This mean

    2. Now I need to store the data of the user form in the month-wise sheets in the workbook i.e. Jan, Feb……up to Dec. with reference to the Date which is “From Date – (minus) Prefix(days)” . For Example, If the From Date is 10 Jan 11 and Prefix is 2 (days), then the Reference date will be (10 Jan 11 - 2 = 08 Jan 11). Now here, the condition is “ If the resultant date’s month is January, then the user form Data should be saved to a Sheet Called January and same way for other months.
    Request please help me on this issue. Many many Thanks in anticipation.
    Thanks and With regards,

    Re: Applying A formula to a range as difference of two ranges having time calculation

    Dear Jindon,
    Thanks a lot. It's excellent and working fine. With a bit modification It has fitted to the requirement. Once again my heartiest thanks to you. i will be looking for similar help in future and thus expect your kind assistance in the same sprit.
    Please do help me in requirements.
    With my heartiest regards,

    Thanks to all members of Ozgrid family,



    Re: Applying A formula to a range as difference of two ranges having time calculation

    Dear Jindon,
    Thanks a lot for your help. The code is running fantastic. But can you just modify the highlighting factor to Pattern Rectangular Gradient in stead of vbRed or any other color. I would like to have a rectangular gradient fill from center of the cell so that the center place is white color and gradually darkens towards the edge of the cell. The macro that I got by conditionally formatting a cell is as follows.

    Sub Macro1()
    ' Macro1 Macro
    ' xyz
    ' Keyboard Shortcut: Ctrl+m
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
    With Selection.FormatConditions(1).Font
    .Bold = True
    .Italic = False
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .Pattern = xlPatternRectangularGradient
    .Gradient.RectangleLeft = 0.5
    .Gradient.RectangleRight = 0.5
    .Gradient.RectangleTop = 0.5
    .Gradient.RectangleBottom = 0.5
    End With
    With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0.349009674367504
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    End Sub

    I have just recorded it on one cell.
    This pattern I want to implement in your code. Can the vb colors be replaced with the same? If so please help.
    Awaiting an early reply

    With regards,

    Dear Friends,
    Thanks for the help provided to me so far. I need a help on the issue as given below

    (a) I have a Work Sheet say Sheet1 having data for 25 plus columns and 90 plus rows. In that Column G (G2 to G90) and N (N2 to N90)contains the time data in [hh]:mm:ss i.e. 37:30:55format.

    (b) In this sheet in Column M (M2 to M90), the difference of (Gminus N) is stored in same time format for all the rows. Means G2-N2 = M2, G3-N3=M3 and so on up to G90-N90=M90.

    (c) I have a Print button on a user form which I want to use for updating the difference i.e. when I click the button, only those rows which are having data in Column G and Column N should give the result in corresponding rows in column M. Means the substructing formula should be applicable to the only rows having the time data. Secondly, I want to highlight the cells in Column M (M2 to M90) having values less than11:00:00 hrs by applying a gradient fill to the cell and making the font bold. Important is to consider the time format.

    (d) Same thing I want to have for Column K which is the Days left and result of Column J - Column P where Column P is the Today's Date. Same Way, I want to highlight the Cell Values having days left less than 11

    (e) I am attaching a sample with this for more details.

    Request please help me out. Thanks in advance to all Ozgrid friends.