range and macro

  1. M

    How can I use a Range Variable in the Advanced Filter VBA Code

    Hi, My project is to create an Excel Personal Accounts WorkBook to replace MS Money. I'm using a Apple Mac Mini with Office 365 - OK with Excel, very new to VBA, so I'm mostly adapting recorded macros. in my 80th year this is challenging - simplicity is key:smile: Project Summary: Sheet 1...
  2. J

    VBA code for emailing payslips from excel source file as pdf's to multiple employees.

    Hi I am rather new to VBA but have used Excel for some time. I have an excel file dump of employee payslips each week. All employees are contained in the one worksheet - structure of data is regular so a search for a key cell value eg "Earnings Summary" at the start of each payslip then create...
  3. G

    Copy data from one file to the active workbook in VBA

    Hello everyone. I've been struggling trying to copy data from a file that I download on a regular bases and paste this data in another workbook. I created a loop in order to select the downloaded file from the folders, after that I try to select the data I need and then paste it in the active...
  4. Rymare

    VBA Populate column with formula

    I have a dynamic range, it changes all the time, and every time a value looks like so: #####-### (or in my formula I put ?????-???), I need this formula: =IF(COUNTIF(A2, "?????-???")>0,MID(A2,5,1),"") to be placed in the corresponding row in column C, because I need to extract the 5th number in...
  5. S

    Update excel VBA to rename tabs based on cell value

    Current: I have code that updates 4 tab (Sheet6, Sheet7, Sheet8, Sheet10) names based on cell values in range D2:D5. This range is populated with formulas, which can result in blanks and is located on a tab named “Calculations”. Each cell is associated with a specific tab: D2 -> Sheet6. If...
  6. W

    Using VBA to copy the values from a filtered range of variable length

    I have the below macro created. It cycles through all sheets in a workbook, except for X, Y, and Z. For each sheet it takes the value in cell A1, and filters sheet ABC based on that value. Then it selects the displayed values from columns A through S on sheet ABC, down as far as row 100, and...
  7. C

    Selecting Range in VBA (easy for you guys)

    I have this code to compare two sheets that will bold the differences between the sheets, the problem is i only want to compare from column A:Q and from row 12 down to infinity. And not the used range as specified in the code below. Hope someone can help thanks in advance. Sub RunCompare()...
  8. F

    Value from Range

    Hi, I want to put different text name for value depend on to which ranges value belong. My ranges: 0 - 15: text name: "AAAA" 6-28: text name: "BBBBB" more than >29 : "CCCCC" I have below code, but I know that my condition are wrong: looking_date = ws.Range("B2").Value if looking_date < 15...
  9. MrJimi

    Copying values to buffer and sending an email

    Hello Experts! I've been having a problem with creating mail sending macro from my report using MS Office 2010. I have come up with an idea to put header of the report in separate sheet “buffer” and then create a macro which will copy row’s range underneath it and then send it all in msg body...
  10. D

    Macro to Copy Formulas to Bottom Alongside a Query Table

    I have an ODBC Query that pulls GL activity from our ERP system into Excel. The size of this data increases the number of rows every month. In columns to the right of this data, I copy down some vlookup and other formulas that I use to organize the raw data and do sumifs on on other pages to...
  11. D

    Help! Sending Excel range via Outlook using sendkeys

    Hi all, Im not the greatest with Excel VBA but i have put together a small programme to send an excel range via an Outlook email using send keys and then to return to excel and do something else. The progamme works perfectly in isolation (Excel to Outlook -End) But when i add excel code to...
  12. J

    Macro button report functionality

    Hi all, I have a workbook that is to be sent out as a sales tool - the user inputs data and the workbook figures out estimated energy consumption - all this is working great and today I've been trying to add a pdf-export function. I have created a worksheet that is formatted so that it will be...
  13. T

    How do you code in VBA to conditionally select and copy certain data from one range to another?

    Hello, I have a table of data where i want to select data that meet a certain criteria and copy and paste that to a table in another range. I.e. I have a table with 6 columns. The first column is date and that is what i am using as a condition. If the date for a given row is in the past week...
  14. M

    VBA Pivot table range

    Hey, new to VBA and I was wondering how I get it to vary the number of pivot lines depending on how much data there is. At the moment it is set to 4 which works most of the time, however sometimes it will only be 3 pivot lines, and the macro reports an error that it is out of range...
  15. S

    Using VBA to name ranges

    Hi folks. I'm trying to use VBA to name ranges, because I will have a lot of them to do (one for each client). If doing this so on a time sheet, an employee can pick the client from a drop down menu, and the next drop-down menu will give codes unique to that client. I'm using Indirect and...
  16. I

    Help with using a formula to define the selected range in VBA

    Hello, I would like to do something like this: Range("=CELL(""address"",INDEX(INDIRECT(R2C12),3,2))").Select ActiveCell.FormulaR1C1 = "=CELL(""address"",INDEX(INDIRECT(R2C12),3,2))" will put the formula into the currently active cell, but I can't get the VBA to select a range based off the...
  17. C

    How do I add a range to a Worksheet change event?

    The following code adds a comment to any cell that has been been change. However, right now it applies to the entire worksheet. Is there a way I can limit the target to a specified range? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Comment Is Nothing...
  18. E

    get the data from a filtered table.

    With the folowwing code, I try to walk trough the filterd rows of a table: Sub GetConfigDataLists() Dim xRange As Range Set xRange = Sheets("config").ListObjects("config_table").DataBodyRange xRange.AutoFilter 1, "Lists" xRange.Select For Each Row In xRange 'Try to read in a...
  19. D

    Using range with cells (copying columns) - Error message

    Hi, I want to create add sheets copying some columns from a "master" sheet using the range command. I am having an issue with a Error 1004 "Application-defined or Object-defined error" when selecting a range using a cell format. I would like to use cells so I coud have the columns in a...
  20. K

    TextToColumns for cells not containing spaces without overwriting cells already split before.

    I wished I was better in VBA because I've been searching how to solve this problem since last friday. I'm hoping you guys can help me out or put me in the right direction. Basically what I need is to find a way to Text-To-Columns, in a fixed range, but only for cells that contain spaces. I...

Some videos you may like

This Week's Hot Topics

Top