vba

  1. J

    Error Handling Help

    I have what is probably a simple error handling issue for most of you but I can't seem to figure it out. I have an excel userform with multiple text boxes that auto populate via a vlookup. This all works fine except for one text box I can't seem to get the error handling right for. I need a...
  2. T

    Data Table Ranges as Dimensions

    I am trying to use VBA to activate a what-if analysis data table and to set up the row and column two factor variable. DT_MFG_Act - is a range name in the Workbook. intJSRow, intActCol, and intGACRow are all integers that represent a row or a column number. This worked yesterday when I...
  3. E

    Automatically populating/removing rows in a table based on the values of another table

    Hello, I'm very new to this forum, just created an account because I've been googling for hours and I can't find a solution to my problem. Hope someone here will be able to help me. I have a 1st table, called "IDStatusSettings" in Sheet 1: This table is displaying datas from other sheets in...
  4. D

    Range.Rows property with autofilter applied

    Hi MrExcel, I need your help. I have an autofilter applied to a range resulting in non-continuous set of rows visible (see snapshot). Assume I want to access the number of the first visible non-header row (which is number 7 in this case). When I run Dim rng As Range Set rng =...
  5. C

    Mail Merge that only acts upon rows that contain data and not functions

    All, I am confident that someone has a solution to the below... I have a mail merge that works perfectly except... It will run each row regardless of content in trigger cell (Name) The data in the sheet being used is all copied form other sheets and consolidated on this one. When I run the...
  6. S

    Pivot table. Hide blank and show top 10.

    Whenever I try to do this it ends up deleting one or another filter. It is very not surprising to me as it filters same columns, But it also happens when I try to filter value over say 300, which seem to be different columns. Is there a way to hide blanks and show top 10 or alternatively hide...
  7. S

    Reduce time taken for VBA loop

    My VBA code currently loops for 5,000 iterations and takes about 25 minutes to collect the output data in the Output excel sheet. Is there a way to reduce the time taken for instance by improving the copying and pasting of values, collecting the output data in some kind of array etc.? Given that...
  8. M

    Disable Overwriting message

    I wrote my code to generate random numbers and every time there is a alert message say "Press Ok to overwrite data". How to turn this off? I tried With Application .DisplayAlerts = False .ScreenUpdating = False .AlertBeforeOverwriting = False End or Application.DisplayAlerts = False BUT...
  9. O

    VBA: how to skip a RANGE of columns when exporting to .TXT?

    Dear community, I have an Excel table to be exported to TXT in Unicode encoding with data separated by tabs (these 2 criteria are important) and i want to skip the range of columns: from C to G. Everything that is before C and after G should be exported. Which VBA code will be able to do that...
  10. B

    populate a range ("a3:h40") into listbox if cell in column "H" is interior.colorindex = 3.

    i want to populate information from range("A3:H40") to listbox only if Cell in Column("H") is colored red. the provided code list them all, even when a cell in column h is not red. Dim lbtarget As MSForms.ListBox Dim rngSource As Range Set rngSource = Sheet1.Range("A3:H19") Dim RNG As Range Set...
  11. T

    Files not saving to newly created folder on OneDrive by VBA

    Hi, I need to create a whole bunch of folders as part of a regular exercise and then save newly created files into those folders. I've tried using MkDir and the "file system object" ways of creating a new folder onto a OneDrive location using VBA. The folder creates just fine, it's just that...
  12. Z

    VBA not completing calculations before moving to the next process

    Okay so apologies if this doesn't make much sense, Mr Excel newbie here... I have a huge problem as I'm trying to automate a report and the report involved holds critical data for daily usage. The problem I'm having is in column A on one of my worksheets (Tails) there is a formula array...
  13. U

    VBA Copy, Move and Rename based on Cell Value

    Hi Guys, I have below data on a sheet with tab name "Data" Date NUM NAME TYPE DATE Subject Note 1 Note 2 10/1/2000 1 Ben Student - Math - - 10/1/2000 2 Ann Student - Science - - 10/1/2000 2 Cher Student - Math - - 11/1/2000 2 Joy Student - Science - - 11/1/2000 2 Sam Student -...
  14. B

    Rename Multiple Work Sheets using Macro based on a single Data Validation Drop Down in one Work sheet.

    Hello, I work for a construction company that does a variety of different projects, and we are trying to streamline our bidding document to cover all city locations as well as all types of projects that we construct. I am trying to create a VBA that renames the worksheet tabs based on the type...
  15. A

    Autofill multiple formulas in columns down to the last row with data in it in column B - ignores column B entries

    Hi I have different formulas in 1106 columns relating to the 1st row (Column B) There are up to 500 rows where data can be put in. This can lead to a lot of formulas, as I have up to 20 sheets of this. The 500 rows are not always used, and I do not want unused formulas in the sheets this is an...
  16. tlc53

    VBA Error if more than 1x spreadsheet open

    Hi, I have code in my main spreadsheet which works fine, no problems at all, until I open another spreadsheet. If two or more spreadsheets are open and I do anything in any of the spreadsheets (select from drop down menu for example) it produces "Run time error 9 - Subscript out of range"...
  17. V

    VBA code works in Excel 2010 but not in Office 365

    I have this vba code that I was running previously in Excel 2010 on windows 7. In a nutshell the code is creating a new worksheet and is copy and pasting the recordset into the new worksheet. Then the code will do a lookup and insert new data into that same worksheet. Recently, the business...
  18. G

    Macro to follow Hyperlink with Ampersand in folder name

    Hi I have created a macro that is set to follow a hyperlink to a folder named, c:\products\Weight&Dimensions. My macro seems to stop at the '&' sign, causing an error as it is looking for a folder called c:\products\Weight. I can't remove the ampersand, so how can I get Excel to understand...
  19. T

    Copy and Paste Rows Between a Recurring Value in Column to Worksheets

    Hello, I'm real new to VBA but I am actively trying to learn so I can automate some weekly tasks I have at a new job. Not money for classes so I am trying to do what I can to teach myself. Currently, one of my tasks has a master workbook with detailed breakdowns of payment for multiple...
  20. 3

    Add a trailing space

    Hi Guys, I have a piece of code which looks like this in order to remove all spaces within text: ActiveSheet.Range("K1:L5000").Select Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart I then need to use the same selection and add in a trailing space back onto the end of the text...

Some videos you may like

This Week's Hot Topics

Top