Posts by rmirpuri

  • Copy Userform input to hidden sheet and next row on other visible sheet

    I have a userform to enter data into a hidden sheet which is a template and only be used to print the information once entered. However that data when entered is supposed to go to various cells for example


    Name : B4
    age : f14
    Height : E10
    and so on....


    Is it possible that the submit button on the userform when clicked would enter the details in the template and also copy the data in the next empty row in another sheet.

  • Open other user form after finding matching record

    Re: Excel Database help


    Thanks Rob. This has solved my problem. Thanks Alot.


    I have 1 more question i do not know if i can ask that in this thread or i need to start a new one. Anyways her goes '

    I am have a user form to enter data into a hidden sheet which is a template and only be used to print the information once entered. However that data when entered is not supposed to go to various cells for example
    Name : B4
    age : f14
    Height : E10
    and so....

    Is it possible to have to add a hidden command button the user form so that once the data is submitted it would go to the template which will be printed and also get copied into another database sheet.

  • Open other user form after finding matching record

    Re: Excel Database help


    Thanks Rob. As i am new to VBA and learning with the help of people here by seeing and understand the examples which they post here.

    IS it possible to only use the View and edit code and use that with another user form which i have created for my workbook.

  • Open other user form after finding matching record

    Hi i found a very good solution here to what i was looking for about a data base. and how to add view/edit and delete records. However in the attached sheet i dont understand :

    When you choose the view option and you search for a plan number ( very small user form with 1 textbox and 2 command buttons) if the match is found how does the other user form open with the data.

    I want to modify the code and the user form a little to achieve my solution.

  • Copy Rows to another Worksheet Based on a Condition

    Re: Copy Rows to another Worksheet Based on a Condition


    If ws.Name <> "Data" Then
    ws.Range("A1").CurrentRegion.Offset(1, 0).ClearContents


    This would also clear all the data from the master DATA file i only want to clear the data from the other files which are assigned to each workcentre.

  • Copy Rows to another Worksheet Based on a Condition

    Re: Copy Rows to another Worksheet Based on a Condition


    Hi Shrivastava

    In the above msg where you suggested to use the following code :

    Sub Macro2()


    Dim ws As Worksheet


    Application.ScreenUpdating = False


    For Each ws In Worksheets
    If ws.Name <> "Data" Then
    ws.Range("A1").CurrentRegion.Offset(1, 0).ClearContents
    End If
    If ws.Name <> "Data" Then
    With Sheets("Data")
    .Range("A1").AutoFilter field:=7, Criteria1:=ws.Name
    .AutoFilter.Range.Offset(1).Copy ws.Range("A2")
    .ShowAllData
    End With
    End If
    Next ws


    Sheets("Data").AutoFilterMode = False


    Application.ScreenUpdating = True


    End Sub


    This would also clear the data from the master Data sheet. Thats not what i am looking for.

  • Copy Rows to another Worksheet Based on a Condition

    Re: Copy Rows to another Worksheet Based on a Condition


    Thanks Bar-B. I am sorry for not being able to explain it clearly. I will try once again with the problem i am facing.

    In the same DataFile which is attached to this thread. :

    Data Sheet is the master Data sheet for all work Centre's 001 to 009. Once you run the Macro, The entier row for each respective work centre is copied and pasted to their respective sheets.

    Without changing or saving the file as sheets have got the data, Do the following:

    Range G2:G8 change the workcenter name 004 and run the macro again, As the Range G2:G8 was changed to 004 that data has been copied in 004 but that same data is still present in 001.

    Problem need help for :

    when the macro is run again Sheet 001 should not have that data as it was assigned to 004.

  • Copy Rows to another Worksheet Based on a Condition

    Attached a sample data workbook which has contains many sheets.

    The data sheet is the master data sheet and in column 7 is the name of the work center. Other sheets are names of the work centre mentoned in column 7 of the master data sheet.

    I have a code which will auto filter the Master data file and copy all data of a respective work centre and paste in the the respective sheet.

    Need help on :

    For example : If yesterday column 7 has 10 (G2:G20) rows with the same work center 001 but however today that needs to be amended to have G2:G8 need to have work centre 003.

    I want the code (mentioned below) to automatically delete the data of G2:G8 in work centre 001 as it would be pasted in Work Centre 003 and in work centre 001 should only have the data of G9:G20.

    PLEASE HELP ASAP.


  • Share a work book base on user name and password

    Re: Share a work book base on user name and password


    Well i asked this because i am using the same code as the excel file attached to this thread only i have changed the names according to my requirement. The reason ask about the save option is because as mentioned before when a user saves the file using the menu buttons, tool bar button or Ctrl +S that unlocks all the files for the user. So if i do not give them the option to save the file working on it they wouldnt be able to unlock the rest and to save the file will only have to close the document. Once they open the document again it is in it original status with all worksheets locked and only can access their document.

    I have one more question which doesnt relate to this but i hope someone can help. Excel suddenly gives me an error saying
    "Path/File access error: '.\VBA2.tmp' " When ever i open any excel document. Ho do i get rid of this issue.

  • Share a work book base on user name and password

    Re: Share a work book base on user name and password


    Thanks for your help. Simon do you have another way that this could be well secure. Since i used the code which i had posted earliar. There seems to be a major glitch to it which i just figured out. That when a user opens the file and enters his/her username and password. When they go to their individual file and press Ctrl + s, all the files get unlocked.

  • Share a work book base on user name and password

    Re: Share a work book base on user name and password


    Thank simon for your help. In the mean while i have managed to find another thread with an example. I have tried to use this code with my work book but the difference is i have a workbook with 12 sheets and when i try to use this code it says invalid sheet names i am all confused as to why.

    Password are :

    Pass for vba editor: admin


    User Pass
    admin admin
    scott 154
    jack 212
    mark 123

    I am looking to creat something similar but i am just being able to.