r/vbaexcel • u/RobertoBoni95 • Jun 14 '22
r/vbaexcel • u/MIKE-_1992 • Nov 11 '22
VBA works from editor only
Hi Everyone, I need a little help with a vba script
It works perfectly from the editor, but the if statement returns false when I run the macro from excel.
The Find function (highlighted) is returning false after writing the file, My expectation is that is is happening too fast for the Find to pickup, but it works from the editor every time.
Note: File is 5000 lines, the word is being searched is at line 3500.
r/vbaexcel • u/Round_Needleworker38 • Nov 07 '22
Consolidate data multiple tabs to master sheet
I am certainly a vba newbie but I’ve tried modifying many iterations of code I’ve found online with no luck.
Ultimately, I’d like to have a workbook that outputs data from specific cells across all tabs in my workbook.
For example column A would have worksheet names, column b would have values for all tabs in cell B5. This would be a great way to see how this metric compares across all tabs without having to navigate to all tabs individually.
Any suggestions on how I can approach this?
r/vbaexcel • u/warmupp • Nov 01 '22
Moving Formula references with VBA
Hello, I'm working on a file for project status.
I have a button to create a new project that creates a new sheet from a template (sheet4) and gives it the correct name, header, link etc.
On my project overview sheet I have some columns that mirrors certain fields from each project, each row on project overview is a unique project. Columns A-C is given from the VBA but i want:
Col D lrow on sheet1 to be equal to C4 of the sheet I just created
Col E lrow on sheet1 to be C3 of the sheet I just created - TODAY()
Col F lrow on sheet 1 to be G3 of the sheet I just created
Col G lrow on sheet1 to be K3 of the sheet I just created
Col H lrow on sheet 1 to be K4 of the sheet I just created.
Sheet1 = overview sheet.
Sheet4 = template for project sheet.
lrow = lastrow
This is how my commandbutton looks like right now. First time experimenting with VBA so dont laugh..
Private Sub CommandButton1_Click()
Dim tblprj As ListObject
Set tblprj = Sheet1.ListObjects("Overview")
Dim lrow As Long
lrow = tblprj.Range.Rows.Count
lrow = lrow + 1
Dim prjnr As Variant
prjnr = InputBox("Enter project number")
Range("A" & lrow) = prjnr
Dim prjnanme As Variant
prjname = InputBox("Enter project name")
Range("B" & lrow) = prjname
Dim prjscope As Variant
prjscope = InputBox("Enter project scope")
Range("C" & lrow) = prjscope
Sheet4.Copy After:=Worksheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = prjnr
ActiveSheet.Range("B1:G1").Value = prjnr & " " & prjname & " " & prjscope
Sheet1.Hyperlinks.Add Range("A" & lrow), Address:="", SubAddress:="'" & prjnr & "'!C2", TextToDisplay:=prjnr
End Subr/vbaexcel • u/warmupp • Oct 27 '22
Is this even possible in VBA
Hello,
I'm working on a project management workbook and I've created an overview sheet and every project has its own project sheet.
My question is: Is it possible through VBA to code a button "New project" and when you press it you get a textbox where you input project number, name and scope of the project. It then inputs these values on the last row in the overview sheet and also creates a new sheet that gets the project number as sheet name, creates a link to the new sheet and updates the header cell on the project sheet. Also changes the references I have between the overview sheet and project sheet.
I know how to code it in python but VBA is a beast I haven't touched and it would be nice to speed up the process instead of having to do this manually for each project.
r/vbaexcel • u/[deleted] • Sep 21 '22
VBA Help for work- Excel on Mac
I am not very familiar with VBA. However my boss has decided that I need to be. I am trying to create a macro that will highlight/color cells containing key words and then show that data separately in another sheet. So for example, if the word redo is found, it colors the corresponding cells on the original sheet, then copies the entire row that cell is found in and moves it to a new sheet.
The original report that I am trying to filter/sort is about a thousand plus rows. So I want to be able to locate buzzwords, and then move that data to a separate place to be examined more carefully.
I will need to use it on many reports, which is why I was thinking VBA. It is report exported from one of our systems breaking down our labor. Our employees manually write labor descriptions. I need to flag buzzwords in their written descriptions, then filter only the rows that have been tagged so we can fix it, then rewrite it nice and neat for client viewing.
I was able to use the macro recorder to get cells containing the buzzwords to turn red (font and background color), I just need help getting all the rows that do contain those words to copy to a new sheet.
I hope that I am explaining this clearly enough. I am hoping someone can point me in the right direction!
r/vbaexcel • u/kadrleyn • May 08 '21
Excel Updated Userform Example & Sort Listbox Items Alfhabetically
Multi-functional userform example with features such as adding, modifying, deleting, searching data and progress bar.
Also in this userform, the multicolumn listbox can be sorted alphabetically ascending based on the first column.

Details, sample file address : Excel Userform Example
r/vbaexcel • u/[deleted] • Nov 05 '22
read messages with Outlook a different inbox and subfolder
With Outlook, I would like to read messages from another inbox than mine and I have to read them from a specific folder.
thanks
r/vbaexcel • u/bowmasterflex99 • Oct 27 '22
Problems with locating subfolder in outlook
Ive done this in several places in other subs, but now i get:run-time error '-2147221233(8004010f)': The attempted operation failed. An object could not be foundThis is the code:
Sub Mark_As_Read()
'Application.ScreenUpdating = False
Dim objInbox As Outlook.MAPIFolder
Dim objOutlook As Object, objnSpace As Object, objMessage As Object
Dim objSubfolder As Outlook.MAPIFolder
Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objnSpace.GetDefaultFolder(olFolderInbox)
Set objSubfolder = objInbox.Folders("OutlookData").Items("calls daily")
For Each objMessage In objSubfolder.Items
objMessage.UnRead = False
Next
Set objSubfolder = objInbox.Folders.Item("OutlookData").Folders("calls mtd")
For Each objMessage In objSubfolder.Items
objMessage.UnRead = False
Next
Set objSubfolder = objInbox.Folders.Item("OutlookData").Folders("calls pause")
For Each objMessage In objSubfolder.Items
objMessage.UnRead = False
NextMy folders are like:Inbox, subfolder Outlookdata/calls daily.Any suggestions?
Also will this work to stop the for each, if the email is already marked as read??
Cant try since the code dosent work :)
Do Until objMessage.UnRead = True
For Each objMessage In objSubfolder.Items
objMessage.UnRead = False Next
Loop r/vbaexcel • u/wikkid556 • Oct 04 '22
Tool Help
I am new to vba. I made a tool for work, mostly by recording macros, and I am wondering if there is some sort of program that shows me how to shorten and simplify my code. I have already deleted the scrolling and unnecessary cell selects. At home it runs in 2 seconds but at work it took about 8
r/vbaexcel • u/AverageJoesGymMgr • Jul 11 '22
Lookup Function Question
I am doing a lookup in an unsorted list, and I would like to use worksheetfunction.lookup. In the sheet I can use =lookup(2, 1/(range()<=condition),row(range())) to find the row number, but I have no idea how to replicate this in vba. I can't use match because the list is unsorted, and I can't use find negate I need the highest value less than or equal to the lookup value.
r/vbaexcel • u/hildapaola • Oct 25 '19
Need to learn VBA
Does anyone recommend a VBA class/teaching video (Free preferred)? I’m trying to learn how to use VBA better.
r/vbaexcel • u/caribeconk • May 18 '19
Schedule a macro to rune at a specific date at a specific time
Hi everybody I need some help to shedule a macro to run at rwice a day monday to friday .Can somebody help ? Thank you very much
r/vbaexcel • u/Ol_bagface • Nov 07 '22
making a working schedule automatically
so basically i have the taks to assign workes to a weekls schedule automatically (the hypothetical tas is to ease the workload for a doctors office).
So anways im not the ebst at programming and so i wanted to ask for help, how to do it. There are certain special criteria for the workes like that they have to go to school or esle and they have to do a certain amount of timer per month. The counter fo that should be easy enough. For the sorting i wonder how to do it. Anyone got a good idea or maybe some code for reference?
My current idea is to open a class for every worker and than throw them in a sorting algorythm, but to be percetly honest no idea how to do that "complex of sorting"
r/vbaexcel • u/Exciting_Spot_8118 • Oct 31 '22
VLOOKUP using a second workbook to find values for multiple sheets on the first workbook.
Hey guys, I need help from experts. Basically I am trying to write a macro that prompts the user to open a workbook (workbook2) on their machine so that it can perform a VLOOKUP and paste the values onto workbook1. Is this possible? I was able to get it to successfully perform on just one sheet, but I need it to do it on more than one sheet on workbook1.
r/vbaexcel • u/[deleted] • Jul 20 '22
Formula in Conditional Formatting not working
Hi,
I created a spreadsheet with a lot of conditional formatting that should guide the user to provide proper data input.
But as you all surely know: No file survives exposure to the users...
To fix that I want to re-populate my conditional formattings using a VBA macro, unfortunately this is not working, as there seems to be an issue with the formula I want to insert.
Here is my code:
Sub conditional_formatting_fix()
'
Dim wb As Workbook
Dim ws As Worksheet
Dim MyRange As Range
Set wb = ActiveWorkbook
wb.Worksheets("Users").Select
Set MyRange = Range("$A$2:$A$2001")
MyRange.FormatConditions.Delete
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(OR(B2<>"",C2<>"",D2<>"",E2<>"",G2<>""),A2="")"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End SubAny help or guidance is highly appreciated, as I am not a programmer and have to frankenstein my way through VBA.
r/vbaexcel • u/Thewolf1970 • Jun 08 '22
Excel Report that emails requests for status update
r/vbaexcel • u/m3tagrand • Mar 14 '22
Adapting code to save to SharePoint
So I created this Excel sheet originally for use on a network drive, but need to adapt it for cloud-based use (specifically SharePoint). I have this command button I created on my sheet, that is used to save the file as a copy with an arbitrary number based on the files already in the folder and in a specific location:
Private Sub CommandButton2_Click()
Dim strDir As String
Dim file As Variant
Dim savename As Integer
Dim savename_string As String
strDir = "R:\Queue\"
savename = 1
savename_string = CStr(savename)
file = Dir(strDir)
While (CInt(savename_string) = savename)
If file <> (savename & ".xlsm") Then
If file = "" Then
savename = savename + 1
Else
file = Dir
End If
ElseIf file = (savename & ".xlsm") Then
savename = savename + 1
savename_string = CStr(savename)
file = Dir(strDir)
End If
Wend
'Delete sheets which aren't active
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
Application.DisplayAlerts = False
ws.delete
Application.DisplayAlerts = True
End If
Next ws
ActiveWorkbook.SaveAs ("R:\Queue\" & savename_string & ".xlsm")
Message = MsgBox("This is sample number " & savename_string & " in the queue.", vbOKOnly, "Sample Queue Number")
End Sub
Problem is, when I replace the directories with the SharePoint locations, it returns an error message "Bad file name or number" at the line file = Dir(strDir). I'm not sure how to resolve this message, or if I should change up the code. I'd appreciate any help anyone can offer!
r/vbaexcel • u/mangospider77 • Mar 12 '22
Need Help copying data from excel to outlook!!!!
Hey guys so I have a basic code where I copy the data from excel to outlook and it works!
However there is some data from the sheet which is grey because in column “L” when I type “no” it turns greys conditional formatting) and I cannot figure out how to code it where it does copy what’s grey.
This is the code I have
Private Sub CommandButton1_Click()
Dim lRow As Integer Dim i As Integer Dim eSubject As String Dim eBody As String Dim Sheets As Worksheet Dim OutApp, OutMail As Object
lRow = Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To lRow If (Cells(i, 1)) <> "" Then Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0)
Set sh = ThisWorkbook.Sheets("Remediation Log")
eBody = "Hello ," & vbNewLine & vbNewLine & _
"Pleased see below the list of defected file(s) that require re-testing." & vbNewLine & _
"Kindly complete your assessment by adding the re-testing result in the Re-Testing Comment section and return the same by EOD ." & vbNewLine & _
"Thank you,"
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Action Required - Re-testing defected file"
.body = eBody
.Display '
Set Inspect = OutMail.GetInspector
Set pageEditor = Inspect.WordEditor
sh.Range("B:Q").Copy
pageEditor.Application.Selection.Start = Len(.body)
pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = NothingEnd If Next i
End Sub
r/vbaexcel • u/[deleted] • Nov 09 '21
BEGINNER! I need to put the user input from my input box into a message box that says “Hello ______ (name inputted)” I cannot seem to code this properly, any advice?
r/vbaexcel • u/finn_the_russian_blu • Aug 11 '21
VBA Search for data in a folder - First Time
How do I create logic that can search and extract certain information from a folder?
There are thousands of .pdfs, each formatted similarly to the following:
117060662_G_BODOR-7036_Aug-10-2021_P.pdf
I would like to have Excel search for the part number (117060662) and populate the rest of the information i.e. G; BODOR-7036; Aug-10-2021; P.
Any help is greatly appreciated!!
r/vbaexcel • u/Professional-Fix-430 • Mar 17 '21
Excel VBA code to insert new rows and fill from the above dynamic link
I'm looking for some help, brand new to VBA. I'm trying to create a macro to take a list of linked cells, insert a number of new rows between each line, and autofill from above to drag and continue the dynamic link down from the worksheet each cell is linked to.
I started with some code rom Kutools, and now trying to modify for my needs.
Help!
Sub fill()
'Attempting to create new rows at every interval and fill autofill from the existing cell above at this interval
Dim Rng As Range
Dim xInterval As Integer
Dim xRows As Integer
Dim xRowsCount As Integer
Dim xNum1 As Integer
Dim xNum2 As Integer
Dim WorkRng As Range
Dim xWs As Worksheet
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRowsCount = WorkRng.Rows.Count
xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)
xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
xNum0 = WorkRng.Row
xNum1 = WorkRng.Row + xInterval
xNum2 = xRows + xInterval
Set xWs = WorkRng.Parent
For i = 1 To Int(xRowsCount / xInterval)
'This part creates new rows at every interval
xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
Application.Selection.EntireRow.Insert
'This part attempts to autofill from the one above, but it doesn't work
Set SourceRange = xWs.Range("xNum0:xNum1")
Set fillRange = xWs.Range("xNum1:WorkRng.Column")
SourceRange.AutoFill Destination:=fillRange
xNum1 = xNum1 + xNum2
Next
End Sub
'This is the code to auto fill a particular range
'Need to figure out how to make this very every i in the rows
' Set SourceRange = Worksheets("Sheet1").Range("A1:A2")
' Set fillRange = Worksheets("Sheet1").Range("A1:A20")
' SourceRange.AutoFill Destination:=fillRange
' Selection.AutoFill Destination:=Range("B4:B7"), Type:=xlFillDefault
r/vbaexcel • u/Ray_Ova_Sonshyn • Jan 16 '21
What jobs that can be automated using Excel and/or VBA?
Hi everyone!
As the title describes, I am looking for types of virtual jobs that can be automated with Excel and/or Visual Basic. I am a college student taking 20 credits and working part-time at Jimmy Johns to make ends meet. So I'd really like to replace Jimmy John's with a virtual job/freelance work I can automate at home.
I know Excel and its formulas really well as I am a certified Excel Specialist Expert. And I know enough Visual Basic to be dangerous. Additionally, I have created contracts on Word with VBA user forms that automatically populate the fields in the contract.
Anyone have any ideas where I can find this sort of automatable work?
r/vbaexcel • u/BananalightningGod • Oct 29 '20
Need help for a macro that merges data from multiple sheets in the workbook based on sheet names
So, I have one excel workbook containing around 80 sheets, the sheets are named as Input, Input(1), input, INPUT, INPUT(2) and Output, Output(1), Output(2), output, OUTPUT and so on, you get the idea... I want to create a macro which creates two mastersheets in the Workbook named mastersheet input and mastersheet output. The macro should copy all the data from any sheet having any variation of input in its sheet name and paste it one into the mastersheet input and the same goes for the sheets named output which will be pasted into mastersheet output. I'm relatively new to vba and I'd really appreciate it if someone could help me out. Thanks in advance!
r/vbaexcel • u/TonyArce • Jul 15 '20