Posts by daren.beaney

    Re: UserForm - Updating active record from user form.

    This is amazing! Thanks for your expert help on this. It makes total sense to do it this way rather than the over complicated and limited bound controls. I've renamed the controls as you mentioned, which does make it easier to identify what they relate to. I can manage with the limitation of 10 columns.

    Thanks again for all your help!

    Re: UserForm - Updating active record from user form.

    Well this is what I have however after it goes through the first .Range... it jumps to refreshing the form which to me seems to be related to the lstMyData (second piece of code).

    The code it bounces to to reload the form:

    The final result overwrites the changes made in the form with data in the worksheet.

    Re: UserForm - Updating active record from user form.

    Thanks for this quick response, that worked a treat with a small tweak.

    Hi, I'm pretty new to forms and I've been struggling with trying to get a user form to update the active record it's displaying. The form includes a listbox displaying the list of records within the sheet and the form fields display the data relating to the selected record in the listbox.

    Unfortunately I can't workout how to use an update button to update the cells regardless whether changes were made or not to the content because it's a dynamic list in the form. I've attached my sample workbook.

    Any help on this will be massively appreciated.

    Re: Searching cells for keyword and moving row to new sheet

    Hi Mumps, I added an additional line to select the existing sheet if it already exists.

    Thanks for your help with this, I'm a very happy man...

    Re: Searching cells for keyword and moving row to new sheet

    Quote from Mumps;682432


    Thanks Mumps, that works great. The idea of not creating a new sheet if it exists is so that if i use the same workbook with new data, it can add new rows to the existing sheet if i use the same foundval. How do I get it to carry out that task. Currently if I repeat the process and use the same foundval, it just highlights the columns w and v on sheet1.

    Re: Searching cells for keyword and moving row to new sheet

    Quote from Mumps;682314

    Hi Daren. Try:

    Thanks that works really well and makes sense. What if I want to create a sheet with the foundval name? But only create the sheet it if it exists already otherwise update the existing sheet?

    Hi,I'm trying to workout how to do the following:1. search the activesheet column G for a keyword that i enter into an input box. 2. when a cell is found containinig that word i then want to select the row and move a copyt into a new sheet.3. repeat the process until all rows have been searched and a new sheet contains all those that matched4. on the new sheet compare the cell in the column containing the keyword and the cell to the left, if they don't both contain the keyword then highlight both cells in yellow.I've attached an example of what the initial sheet would look like and have struggled to find anything close to what i want to do.Thanks in advance for any help you can provide.

    Re: Update multiple worbooks from 1 workbook

    Hi Smallman,I did say that this was working however I cant get it to work...I've checked the sheet names and file names, even created files with the exact sheet name.Can you see any issues in my code below?Thanks,Daren

    Dim sPath As String    Dim sFil As String    Dim strName As String    Dim oWbK As Workbook    Dim sh As Worksheet    Dim ws As Worksheet    Dim Temp As String    Dim shNm As String    Temp = InputBox("Enter Sheet Name")    sPath = "G:\Master Files for HRBP" 'Change path to suit        For Each ws In ThisWorkbook.Worksheets        shNm = ws.Name        sFil = Dir(sPath & "*" & shNm & "*.xls")                Do While sFil  ""            strName = sPath & sFil            Set oWbK = Workbooks.Open(strName)            sFil = Dir            Set sh = Worksheets.Add            sh.Name = Temp            sh.Move After:=Sheets(Worksheets.Count) 'Pastes to end            ws.Range("F1").CurrentRegion.Copy            sh.Range("A1").PasteSpecial xlPasteValues 'Do you want the vals pasted?            oWbK.Close True        Loop    Next

    Re: Update multiple worbooks from 1 workbook

    Hi Smallman,Thanks for the reply!I tried the code and it seems to work fine to a point and reason being is my file names have other text i.e. 2011-2012 DPM CDG v12.xls it doesn't seem to find the file containing CDG. Is there anyway it can search for the file that contains the sheet name in the string?Really appreciate this!Daren


    I have a master workbook called "APR Compliance Master.xls" and in this workbook I have 27 sheets with a name such as CDG. I also have 27 workbooks in a folder location on our network for example called f:\Test update files.

    What I've been asked to do is see whether it's possible to:

    • use an input box to define the new sheet name for each workbook (which will be the same name for all workbooks eg "Sept")
    • go through each sheet in my master file, copy the content in the currentregion and then...
    • go to the folder location above and find the file that contains the master workbook activesheet name such as CDG (the file also has additional text such as version)
    • create a new sheet at the end and use the name from step 1
    • paste in the data copied and save and close the workbook
    • go back to step 2

    Hope that makes sense. I've been searching for weeks on this but can only find ways to merge content from multiple workbooks into a single sheet.

    Kind regards,

    Re: For Each Worksheet in... not moving to next worksheet.

    Hi,thanks for the quick reply!! I've used rory's code and it works well except it overrights the header in column 'H'.The code starts off in Index, using the data from within Master and Data sheets and creates new sheets from the information contained within them.also the sheet names are in mixed case i.e. 'Master' but rory's code works fine, can anyone explain why?ThanksDaren