vba

  1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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 -...
  8. 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...
  9. 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...
  10. 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"...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. Dave_O

    VBA: Excel Uppercase words between HTML <strong>

    Hello, I am saving a website's source code as a text file, then importing it into a worksheet. I currently use a UDF to vbCrLf existing Uppercase word's, but I would like to make all words Uppercase between <strong> .....<\/strong> I am OK with either VBA or UDF. SAMPLE: As is...
  16. N

    Excel Live Data Refresh - Stop all popups and errors

    Hi All, I am pretty amateur at Excel VBA, so please bare with. I have an excel workbook, this contains lots of worksheets with live data in SQL. The issue I have, is each worksheet is set to data refresh every 5 minutes, independently. Sometimes when it refreshes, it will give an error about...
  17. K

    Copy Multiple Files from Absolute Paths to SharePoint with Same File Names

    Hi, Firstly, I apologise if this has been answered multiple times but my google searches, although provided ideas, have produced as many questions as answers. In the Excel file where the code resides, I have an excel table that has a list of absolute file paths (SharePoint). They are in...
  18. N

    For Mass email using custom body range

    Sub Send_Mails() Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("Send_Mails") Dim i As Integer Dim customBody As Range Dim OA As Object Dim msg As Object Set OA = CreateObject("outlook.application") Dim last_row As Integer last_row = Application.CountA(sh.Range("A:A")) For i = 2 To...
  19. 3

    VBA - Combine Multiple workbooks into 1 worksheet

    Hi, I am trying to write a VBA script which will combine multiple workbooks into 1 worksheet, copy and pasting all columns as values. Each sheet from the multiple workbooks should be pasted below the last, and ideally there would be the workbook name pasted into column A of the master as a...
  20. B

    Integrate VLOOKUP in VBA

    Hello everyone, I have a workbook with two worksheets namely "1" and "Sheet1". I would like to write a macro which integrates the VLOOKUP function. My working function is as follows: =IF(H2="";"";VLOOKUP(H2;Sheet1!A:C;3;False) but I am kind of stuck with how to get it to work in VBA. In...

Some videos you may like

This Week's Hot Topics

Top