Questions tagged [vba]

Visual Basic for Applications; a subset of Visual Basic used in all Microsoft Office applications. Consider using this tag together with the tag for the specific application in question. Good questions on VBA generally contain some code and asks for solution of specific issues. Questions asking for entire solutions (please give me the code / how to do this and this with VBA) are off topic and will be closed as too broad.

Filter by
Sorted by
Tagged with
0
votes
1answer
16 views

How to batch-convert MS Excel files page size from letter page size to legal page size using VBA

I have 200 Excel files in one folder, with file extensions .xls, .xlsx, and .xlsm. They were all typed in letter page size and I need to convert all of them to legal page size (landscape orientation). ...
1
vote
1answer
13 views

Excel VBA - Post with empty cell values

I have a Macro that takes text entered into a textbox and posts it to the next empty row. This simulates a running list. The problem is that if the textbox is left blank, the next time the user clicks ...
-1
votes
0answers
13 views

How to remove emojis from cells range when they are text

For this VBA macro how can it work for a cells range? I would like to iterate this code over a range of cells instead of just the active cell. Sub kleanIt() Dim r As Range, v As Variant, L As ...
0
votes
0answers
18 views

Split data into tabs based on column

I found this code online where you can split data into different files based on column. Is there a way to use this wherein instead of creating new files, it will create new tabs within the worksheet? ...
0
votes
0answers
23 views

How to split data into tabs based on column?

I tried using the following code I found online. It mostly works fine. However, when the data in the column has an invalid character or length for a Sheet Name (e.g. Accounts payable and accrued ...
0
votes
0answers
17 views

MS Access Linked Excel File “External table is not in the expected format.”

I have a intermittent problem with Access not being able to read simple linked excel files. I've been using the same processes for years without issue. Now, when calling an Access Macro from an Excel ...
0
votes
1answer
25 views

Error Handling in VBA

I am creating 2 arrays in VBA based on the values present in Column number 16. However some of the values in the column are error values (#N/A). I want to check the value of the column against some ...
0
votes
1answer
19 views

MS Access 2013, where is my toolbar and vba editor?

I am trying to get into the codes for my access file, my file has no toolbar options, do I have some light version of access or something? or is this file locked? Was trying to get to full options ...
0
votes
0answers
31 views

How to replace formulas into values but with possibilty to remain referenced to its original source (dynamically)

I would like to have the possibility to convert some formula (in my case CONCAT) into values, but with the possibility to select those values and not to have formula shown, and also to be able to ...
0
votes
1answer
33 views

Detect cell format change

How can I detect a change in the fill colour of a cell in a column in a table, I have tried using OnChange like; Worksheet_Change(ByVal Target As Range) ...... If Not Intersect(Target, Me.ListObjects(...
2
votes
1answer
28 views

VBA to hide certain rows based on cell value

I have some information in rows 9 to 38. I need to hide these rows based on a cell value, for example A8: If A8=3 then only show rows 9 to 11 out of 38 if A8=9 then only show rows 9 to 18 out of 38 ...
0
votes
2answers
98 views

How to pivot (transpose) data sets of differing length

I have 5000+ records in Column A. Each record contains a number of rows of data. The number of rows containing data varies from record to record. How do I transpose the data in these records so that ...
0
votes
0answers
27 views

Saving content of individual cells within a column as separate JSON files

Is there a way to create a collection of JSON Files in a folder based on the content of individual cells in a column in excel? So basically, I have a spreadsheet with a column starting with cell B12 ...
3
votes
1answer
39 views

Excel VBA: Application.Tranpose on an array gives Run-time error 438

I have a VBA code that gets data from a column of a structured table and puts it in a column of another structured table like this: Dim TopNTickers As Variant Dim NoOfTickers As Integer Dim ...
0
votes
0answers
48 views

SAP process session txt no taking input column

We have an Excel VBA that generates a .txt in order to make postings faster. However, there is field that is not taking the field even if it is generated in the .txt: As you can see, when checking ...
0
votes
1answer
34 views

Excel: Is it possible to select multiple values in Drop-down list? (without VBA)

Is there any possibility to mark more values in drop-down list and to have them in one single row? This is the way I found but still it is not inside a single row... My DDT And this is actually ...
0
votes
1answer
55 views

creating multiple folders using excel VBA

Sub Datasheet_MC_V1() ' ' Datasheet_MC_V1 Macro ' Dim Filemane As String n = Sheets("Loop").Range("C1").Value For i = 2 To n + 1 Sheets("Loop").Select Range("A" & i).Select Selection....
0
votes
1answer
31 views

Split a large excel workbook with multiple worksheets into multiple files based on a column

I have a workbook with multiple worksheets with about 200000 rows. Each worksheet has a state column, and the states are common in all worksheets. I need separate files created based on the state ...
0
votes
0answers
37 views

Excel freezes while running macro

I have a module which gets data from a web API and then copies that data to the excel sheet, while this macro is running, all the other excel sheets freezes for a couple of seconds. Any help to ...
0
votes
1answer
49 views

Check if Value comes exactly 2 times in given selections

I need to check that in the given selections, my value should be there only 2 times. ]Image showing it all? Item1 Item2 Item3 Item4 22 24 3 15 26 21 18 19 ...
0
votes
1answer
29 views

How to update one cell's value, with the latest entry in a particular row?

I would like to update one cell value, when change occur in a specified range. e.g.: if I type in "asd" on WorkSheet1 anywhere within Range A1:A100 then on WorkSheet2 A1 would be updated to "asd" ...
0
votes
1answer
74 views

Excel Formatting Data Validation Input

I have a workbook with a list of sheets all in a date format (5-18-20, 5-19-20). I have a VBA function that will collect this list and put it in a comma-separated string. But whenever I try to set a ...
0
votes
1answer
42 views

Check Cells - If Cell 2 have contains Specific different text then stop from saving

I'm new to vba and have been figuring things as I need them. Any help or clue would be greatly apperiated! I have a new requirement for my macro. I have a Macro that will copy a ws, create a file name,...
0
votes
1answer
37 views

Is “Extract” a reserved word in Excel or VBA?

Is "Extract" a reserved word in Excel-2016 or VBA for Excel? I was writing a custom function to find and extract a portion from a complex set of tuples. Naturally I named the function Extract and got ...
0
votes
1answer
16 views

Using VBA in Word, How to clear/update/change/reformat the Style of a Footer

I am trying to insert a footer into each page of a Word Document. Sometimes the following code works, others it doesn't. The issue: The Footer style is defaulting to a style such as Header1 or Header2,...
0
votes
1answer
16 views

How to Add rows to a table based on the number input from a single cell

I am new to VBA and while I can typically search out an answer for what I am looking for, I have not been able to find anything for the scenario I need that works. Scenario: I have a workbook where “...
0
votes
0answers
23 views

XML to Excel and vice-versa

First of all, I am really not quite familiar with XML data, I mean I didn't work with them. I just got a question if I can do something with them using Excel, try to automate some things. I could ...
0
votes
1answer
54 views

Manually run macro in powerpoint presenter view

TLDR: Can't run macros from powerpoint presenter view. If you're not interested in the background, just skip right to "What is not working". I've painstakingly created some macros that I want to run ...
-1
votes
1answer
27 views

Accidentally closed excel vba file without saving

I was prototyping a vba macro and had two files with datasets. One was just dataset and the other was dataset with the macro. When I was about done for the day I selected the wrong file to close ...
1
vote
1answer
53 views

Outlook Rules Sending Auto-Reply Over Specified Time Range

I am seeking help with Outlook Webmail. Specifically I am trying to set up auto-reply messages during certain times of the day. Outlook offers out of office emails, but I am trying to set up rules ...
0
votes
1answer
22 views

Compile error: Next without For

Searched but I think my code is correct now, but still get this dreaded compile error, but I cannot determine why! Code is as follows: Dim LastRow As Long Dim i As Integer LastRow = Cells(1, "A")....
0
votes
2answers
20 views

Canned auto email forward (with attachments) in Outlook 2013

I get emails sent into me from one sender (with attachments). All I do is forward (not reply) that email to another recipient with a short sentence (always the same). Surely this could be automated .....
0
votes
0answers
32 views

Excel: how can I find the closest date in 3 distinct lists, where all 3 lists have the same date present

I'm trying to automate a reporting process where by we summarize data from 3 separate reports for the most recent Friday. Sometimes for whatever reason one or more of these 3 reports won't run; at ...
1
vote
1answer
59 views

Excel VBA send e-mail with variable number of attachments

I am trying to write some VBA code that will open outlook and attach all the PDFs in a certain folder to the email. I know how to do it for one item, but since I don't know how many items the folder ...
0
votes
1answer
26 views

VBA Error in referencing range of another sheet via the Cells option

I have some subs I use to format data in an Excel file. In particular, I have a sub that I can activate in the sheet("1.Parus") that has the line : Range(Cells(2, 1), Cells(1000, 3)).Interior.Color ...
0
votes
0answers
29 views

Winword: Connection to Word Add-In (dotm file) does not stick after closing and re-opening Word file

I have written some Word macros stored in an .dotm file saved in a folder on my computer (not the default personal folder of Word). I then went to File - Options - Add-Ins - Word Add-In and connect ...
0
votes
1answer
41 views

Enter time duration in Excel in mm ss format (without hours and colons)

Enter time date in excel in mm:ss format(just buy inputting numbers without the colon : and 0: hours in front) Hi, I'm working on a project and need to calculate the number of minutes and seconds ...
1
vote
1answer
44 views

Date format to string in ddmmyyyy

So I am working on an existing workbook so I cannot change the layout unfortunately. In the first sheet, a function automatically returns today's date in the "yyyymmdd" format. On another sheet, that ...
2
votes
2answers
1k views

How can I place a date in dd/mm/yyyy format into an Excel cell with VBA?

I want to fill a cell in a worksheet with the date as string and I want it to be in the dd/mm/yyyy format. I use the following code but it keeps showing as mm/dd/yyyy. I'm not sure what I am doing ...
0
votes
1answer
34 views

Make hyperlink in Excel only display word (single part of hyperlink)

I have hyperlinks that refer to different items and the initial part of the hyperlink is all identical. What I am trying to do is put or display that hyperlink using only what comes after the last "=" ...
0
votes
0answers
45 views

Is a macro the best solution?

Im looking to have excel display all totes that have an expiration date less than or equal to todays date. It seems using a function is not an efficient option...so would using VBA be a better option, ...
0
votes
0answers
19 views

How to reference a value from the n th in worksheet and move to the m th row in different worksheet

I have a group of data in worksheet 1 which is under a similar category every 4 rows 9 (i.e category A) I need a copy/Reference of the group/blocks data in a worksheet which as a pattern for every 25 ...
0
votes
1answer
36 views

Excel vba method, “source.copy destination” results in something mysterious

This is the macro: Sub dddExport() Dim sFile As String Dim rSource As Range Dim rDest As Range Set rSource = ThisWorkbook.ActiveSheet.Range("a1") sFile = "C:\dann\dan.xlsx" ''''...
0
votes
0answers
28 views

Excel - Formula in shape recalculates on open

I have a shape in Excel which is assigned to macro which resets filters. When filters are set, there's a formula in the shape which makes it say: "Filter set". The formula goes like "=G1", not more. ...
0
votes
0answers
18 views

How to move specific cells in a row from one worksheet to another based on Month in Month Due column

I have been googling for a while trying to find a solution for how to copy specific cells in a row from one sheet to another. I have only been finding ways to copy entire rows. I have a workbook and ...
0
votes
1answer
18 views

How do I make this macro run on the sheet which is open?

The macro deletes columns I don't need, by deleting all of them except the ones with names specified. I can't work out (and it should be easy) how to just make it work on any worksheet, not one with ...
0
votes
1answer
21 views

Need to make restriction in the VBA function

I want the below function to pick data from Row till Column H, however this function is picking details up to the last used cell. Below are the VBA code I'm using, help me to fix this. Function ...
0
votes
2answers
23 views

How to hide different rows based on two seperate drop down boxes?

I am sure there is an easy fix to this, but I cannot figure out why the first if statement is working and the second one is not. The 1st statement to hide 57:68 works but the second statement of ...
0
votes
1answer
22 views

Understanding this unusual Excel VBA range statement

I've come across this rather unusual Range("DataTable[#All]") syntax in some code: Sheets("Data").Range("DataTable[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("AK1:BQ2"), ...
0
votes
1answer
19 views

Count Cell Color with Count IF critiea VBA

Hey all i have a range of data i wish to count by cell color and if it contains a certain value (the month of April this year). This is my current Formula =CountCcolorIF('2019-2020'!I3:AT3,'2019-2020'...

1
2 3 4 5
64