Posts by yjoshi

    Following code will most probably solve all your issues.

    I have tested it myself.
    Let me know if it does not work :yes:

    I have attached a sheet in which i tried to create the scenario of copying the formula above the cell instead of having fixed formula.

    Private Sub CommandButton1_Click()
    Range("A65536").End(xlUp).Offset(-2, 2).Copy
    Range("A65536").End(xlUp).Offset(-1, 2).Select
    End Sub


    Figured out what is the problem...

    If the application is in "Design Mode"
    Generally First button in "Command Bar"
    then the copy paste of rows does the trick.

    But could not find a VBA code to enable or disable this "Design Mode".

    Will investigate further.. but should give a good headstart for someone else who wants to jump into this.

    Sorry... but Highly confusing.

    Will File -> Print Area -> Set Print Area work for you?

    If not, please specify your problem clearely, and what do you mean by printing in two columns??

    Hi MKNOV,

    Thanx for your sincere appereciation of a solution which solved the problem of the original query of perryco, by saying ...


    It makes vague sense to me, but due to being generally useless I need more help!!

    As the original post is solved, i do not want to complicate this post.

    Please create another post with details of your problem, and we will make sure that it will not be a "generally useless" solution.


    Here comes the code.....

    Also attached a file for demo

    Hope this helps

    Hi chickenflicker,

    Welcome to the borad.

    Just for clarification, Window -> Hide is used to hide the entire worksheet.

    If you want to hide a particular column(s)
    then Click on the Column header(s), then
    Format -> Column -> Hide
    (OR Just select the columns, press right mouse key and select hide)


    Try this

    1. Add One Column (Co1 E) called concatinate after the meal.
    2. Paste following formula in E2 and copy till your record end
    3. This will give you a unique record for each date, meal and batch (As each employee can have each meal only once)
    4. Now in Col F (Count) add following formula
    5. This will give you the count of such unique record, which should be only = 1.
    6. Now enable either An Auto Filter or an Advanced filter to weed out the employees where the count is greater that 2.

    No Need for Pivot table here man, just keep it simple.

    If you want this to be completely automated with VBA, post back.

    Attached sheet for your reference.

    I am touched by your modesty!!!!!

    I was thinking of writing this, but you did it, and there lies the difference.

    You said it so well, that it cannot be said better.

    Please accept my best regards, and just to let you know, you are surely special, and you make it feel by your replied.

    Great Going Guy.... (GGG)

    Hi Jethro,

    You look to be determined to get your entire application developed here, but don't mind i like working like this :)

    Its really good that you are coming up with interesting questions one after the another.

    I have taken the sheet from your previous post,
    so that you will not loose that work. Also, I have proactively added two additional columns in the index apart from Sr. and Sheet Name
    1. Description : Currently linked with cell A1 on each sheet
    2. Count of records : Currently linked with Cell H1 on respective (which is formatted as ;;; so not visible) which gives the count

    I have also amended the Overall Sheet Copy formula to take care of this Index page, other wise, it will messup that!!!

    I have also taken the liberty to copy the image you use for Avatar on this site and pasted it on index, and linked the macro to that image, just for fun :D

    Here is the code for Index,

    Sub create_index()
    'By Yogendra Joshi
    Dim sht As Worksheet
    Dim lnk, lnk_show As String
    Dim cnt As Integer
    'Clear Existing Data

    Application.ScreenUpdating = False
    If (ActiveCell.SpecialCells(xlLastCell).Row) > 8 Then
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Clear
    End If

    'Checking for Worksheets and copying data
    For Each sht In Worksheets
    If Left(sht.Name, 1) <> "_" And sht.Name <> "Index" Then
    With sht
    lnk = "'" & .Name & "'!A1"
    Debug.Print lnk
    lnk_show = .Name
    Sheets("Index").Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveCell.Value = (Selection.Row) - 8
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = .Name
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, _
    Address:="", SubAddress:=lnk
    ActiveCell.Offset(0, 1).Value = .Range("A1").Value ' add your own cell if needed
    ActiveCell.Offset(0, 2).Value = .Range("H1").Value ' added a hidden cell for count
    End With
    End If

    Application.ScreenUpdating = True
    End Sub


    Hope this helps...

    Please do keep coming with more such brellient ideas, someone here will surely put it to reality. :cheers: