Posts by gijsmo

  • Find function in VBA

    To avoid skipping over the first value when using Find, set the After parameter to last cell in the range eg:


  • VBA to force save excel document as macro-enabled & auto populating filename as reference in a cell

    Not sure what the purpose is of asking the user to specify a filename in the following code:

    Code
    1. xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")

    It just seems to be a trigger to confirm saving the file.

    xFileName is not used in the code, the following code overrides whatever filename is chosen with the value of ThisFile:

    Code
    1. ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    The only thing I can see that GetSaveAsFilename will do is maybe change the destination folder of the saved file.

  • Data input from user form not inserted into excel

    Without changing loads of code you've already created, one way to do this could be as follows in place of your "For" loop in the AddRecord routine.


  • Data input from user form not inserted into excel

    The values of Production Output and Total Rejection are calculated fields, derived from the Material In and Reject values.


    The AddRecord code is clearing controls:

    Code
    1. 'clear control
    2. ctrl.Value = ""


    Therefore, Material In and Reject values are being cleared after being written out to the sheet which is resetting the Production Output and Total Rejectionvalues to 0 before being those values get written to the sheet.


    Rather than looping through the controls, might be best (and more efficient) to write out the specific control values to the sheet.

  • Converting Address to usable range

    You seem to be wanting to copy a column from one workbook to another however you also mention NB:NG which is obviously multiple columns.

    To create a range for the single column containing the "ORGANIZATIONRow1_2" header ie, from the header row (row 1) to the last row in the column, try the following (the range rCopy contains the data to be copied).


  • Macro code to fetch only .pdf files from the folder

    Try this


  • Routine loops through files in folder, progressively slows down resulting in workbook crash.

    The StatusBar showing as TRUE is fixed by changing the code at the top from:

    Code
    1. .StatusBar = True

    to:

    Code
    1. .StatusBar = False

    Also, you should add

    Code
    1. .StatusBar = False

    to the group of code at the bottom of the routine to turn it back off.

    Not sure why it would only show 100% though.


    One other thing to try - this used to be done easily with VBA Code Cleaner but you can also do it manually.


    Save a copy of your file as an .XLSX file, thereby removing all the macro code.


    Then re-open the .XLSX file and copy/paste the macro code back in. This can be a bit time consuming if you have code in multiple modules but it might help 'reset' Excel, a lot of 'trash' can build up especially if you've been making lots of code changes and/or running the code for a while.


    When you've pasted the code back in, save it back as an .XLSM file.

  • Routine loops through files in folder, progressively slows down resulting in workbook crash.

    I cannot see any obvious reasons for the code to cause a memory loss/crash.


    A variant of the main routine which may speed things up a bit is below.

    I've made no changes to the GetFiles routine other than some formatting for readability.


  • Macro to copy selected sheets based on filtered cell values to new workbook

    There are a number of ways to do this.


    Going mostly with the code you have, I have the suggested changes below to the Filter/Extract section.


    This requires new variables for wsSht, rRange and rCell.


  • Find column number of most recent date in row to be used as a variable

    An alternative using the Range.Find function to find the max date value is below.


    This assumes row under ActiveCell in the ActiveSheet is to be searched.


  • Copy data from Multiple workbooks simultaneously into One excluding first row

    This request comes up fairly frequently on the forums.

    A while back I wrote a generic multi workbook copy standalone macro that can:

    1. Copy a single (specified) sheet from each workbook into a single workbook
      If no sheet name is specified, the first sheet in the workbook is assumed
    2. Optionally copy only the header from the first workbook into the output workbook
    3. Loop through all files in the input folder to build the output workbook

    The macro is in a standalone file so no need to copy a macro into another workbook.


    MultiWorkbookCopy.xlsm

  • Excel VBA extract specific text from cell

    Maybe try something like:

    This assumes of course that the filename you are trying to extract before the ".bmp" extension does not contain any other full stops eg, CBend.0.bmp would only return the CBend value.

  • MsgBox code running & giving type mismatch error in middle of code.

    It looks like you have misspelled 'Address' in the Target property name. Also make sure the underscore character is at the end of each line.
    Try :

    Code
    1. MsgBox " Check to verify Veteran data is entered in FY ## Referrals." & vbCr & _
    2. " It's critical that carryover data is captured. " & vbCr & _
    3. " Please enter the Name in the walk in list if not on either last year's or this year's consult's list! " & vbCr & _
    4. " You have entered a name in cell " & Target.Address, vbInformation, "Vocational Services - OVR " & ActiveSheet.Name