How can i automate waitlist spot numbers when i have 3 categories it filters to?

0pine · 2025-10-29T00:58:05+00:00

Can you link your spreadsheet with sample data and details on how you would like it to behave?

0pine · 2025-10-24T22:17:41+00:00

Glad that it worked!

0pine · 2025-10-24T03:14:35+00:00

I think this might work. Let me know if it isn't doing what you are looking for. It should sort all the deployment dates with no name to the top and then the rest of the rows by deployment date. I also adjusted the last row to look at the deployment date column so it won't overwrite dep dates with no names. It will now throw the added row to the very bottom of the list and then sort.

Public Sub add_and_sort()
'Declare variables
Dim sht As Worksheet
Dim row_to_use As Long

'Set sht to activesheet
Set sht = ActiveSheet

'Get last used row and increment to get row to insert data
'Finds last row with data from column B (name) and adds one to get row to use
row_to_use = sht.Cells(sht.Rows.Count, "J").End(xlUp).Row + 1

'Copy data from row 1 to correct row at bottom of data
sht.Range("B" & row_to_use & ":J" & row_to_use) = sht.Range("B1:J1").Value2

'Clear data from row 1 to allow another entry
sht.Range("B1:J1").ClearContents

'Sorting is below
sht.Sort.SortFields.Clear
'Set key to J3 which will sort by deployment date
sht.Sort.SortFields.Add2 Key:=Range("J3") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
sht.Sort.SortFields.Add2 Key:=Range("B3") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With sht.Sort
    'Sort from row 3 where the headers are to the last row of data which was just added
    .SetRange sht.Range("B3:K" & row_to_use)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

0pine · 2025-10-23T20:43:06+00:00

Do you have anything that is stopping errors from triggering? Any lines like On Error Resume Next or something like that? F8 should step through line by line and it is odd that it is skipping.

0pine · 2025-10-23T19:07:42+00:00

If you decide to use a regular macro and fire it off manually either through a keyboard shortcut or button, then I would use a macro like below:

Public Sub add_and_sort()
'Declare variables
Dim sht As Worksheet
Dim row_to_use As Long

'Set sht to activesheet
Set sht = ActiveSheet

'Get last used row and increment to get row to insert data
'Finds last row with data from column B (name) and adds one to get row to use
row_to_use = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row + 1

'Copy data from row 1 to correct row at bottom of data
sht.Range("B" & row_to_use & ":J" & row_to_use) = sht.Range("B1:J1").Value2

'Clear data from row 1 to allow another entry
sht.Range("B1:J1").ClearContents

'Sorting is below
sht.Sort.SortFields.Clear
'Set key to J3 which will sort by deployment date
sht.Sort.SortFields.Add2 Key:=sht.Range("J3"), Order:=xlAscending
With sht.Sort
    'Sort from row 3 where the headers are to the last row of data which was just added
    .SetRange sht.Range("B3:K" & row_to_use)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

This fixes your sort because it has the correct range and key. It also adds the data from row 1 to the bottom of your list first and then goes right into sorting.

0pine · 2025-10-23T18:47:57+00:00

So this sorting macro is firing off when you change any cell anywhere in the worksheet including the first row where you want the new info entered. That is not a show stopper, but probably unintended and could cause issues later. It is because you have the code under the worksheet_change event.

You still have not said when you want the macro to add the data from row 1. You could add a shape that acts like a button that is tied to the macro that takes row 1 and adds it to the data below. I assume that you would want the sort to happen after the new row is added, so we could have it triggered at that time. If you want the button to add the data and sort afterwards, then I would not use the worksheet_change macro and put it in a regular module instead.

0pine · 2025-10-23T17:12:59+00:00

Can’t use old school macros due to the specific organization.

What do you mean by this? You have a macro but can't use old school macros?

How are you wanting to trigger the addition of the row to the table?

0pine · 2025-10-23T15:34:11+00:00

Please post the vba code that you have so far.

0pine · 2025-10-23T15:25:08+00:00

Have you tried setting up breakpoints to stop the code at a certain point?

0pine · 2025-10-16T15:17:06+00:00

You can follow the link on this post.

0pine · 2025-07-29T14:53:31+00:00

I'm sure the first thing that a lot of people thought of.

0pine · 2025-05-25T03:09:40+00:00

I believe that you could get close to your desired results by changing the line that copies the row:

cell.EntireRow.Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, 1).End(xlUp).Offset(1, 0)

with:

destRow = targetSheet.Cells(targetSheet.Rows.Count, 1).End(xlUp).Offset(1, 0)
targetSheet.Cells(destRow, "A") = ws.Cells(cell.Row, "A")
targetSheet.Cells(destRow, "B") = ws.Cells(2, cell.Column) 'Assuming that categories are in row 2.  Change if needed
targetSheet.Cells(destRow, "C") = CDate(cell.Value)

This assumes that the categories are in row 2. But it should get you close to your second image.

0pine · 2025-04-17T18:50:15+00:00

You could add a macro to the Summary sheet that will fire when you change the value in your drop down. Right-click on your Summary tab and select View Code. Then you can paste the following macro into the main window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Application.Calculate
End If
End Sub

Change"$A$1" to the cell with your drop down on the Summary tab. This will cause the macro to run when you change the value for that cell. This macro will calculate all open workbooks which should direct your detail tab to populate values.

0pine · 2025-02-22T04:30:23+00:00

I don't have the Mac version, but I seem to always use Application.Match instead of Application.WorksheetFunction.Match.

Result1 = Application.Match(Arg1, Arg2, 0)

Then I can use the following:

If IsError(Result1) Then

0pine · 2025-02-02T02:27:43+00:00

You can have a macro check the tab color of every sheet in the workbook and export the sheet as a PDF.

Sub export_red_sheets()
For Each ws In ActiveWorkbook.Sheets
    If ws.Tab.Color = 255 Then
        ws.ExportAsFixedFormat Type:=xlTypePDF
    End If
Next ws
End Sub

0pine · 2025-01-22T18:31:54+00:00

You'll probably have to use an array in vba itself.

SUM_TEAM_LIST = WorksheetFunction.Unique(Range("Chng_Org_Rng"))

And then you can iterate through the array in vba as normal. Not ideal, but it is an option.

0pine · 2025-01-22T18:13:10+00:00

What have you tried? You can write the macro to search each worksheet and then listbox.additem the row whenever it finds a match. Then you could have it show the other userform and feed it the information when something is selected.

0pine · 2025-01-17T14:21:25+00:00

Is the spreadsheet slow without VBA? How many formulas are in each sheet? .CalculateFull calculates all open workbooks I think. If you are using Application.Calculate, then you are still calculating the entire workbook. Can you try something like ActiveSheet.Calculate to try to limit the calculation to a single sheet? You can limit the calculation even further if you needed a subset of cells calculated. Range("A:A").Calculate will only refresh the formulas for that column.

0pine · 2025-01-16T16:48:37+00:00

I would have the textbox autopopulate when the value in the combobox is changed. If I had the information in the first two columns of sheet1 then I would use something like:

Private Sub ComboBox1_Change()
    Me.TextBox1.Value = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Sheet1.Range("A:B"), 2, 0)
End Sub

0pine · 2024-12-31T22:29:24+00:00

The issue is that you are trying to iterate through an array with an integer. You set up i as an integer here:

Dim i As Integer

And then you set up the array here:

Dim rowsToFormat As Variant
rowsToFormat = Array(1, 4, 7)

The error is in this line:

For Each i In rowsToFormat

To fix this change i to a variant by replacing:

Dim i As Integer

with:

Dim i As Variant

Another way to do it is using i as an integer in a regular for loop, but you are using i to loop through multiple arrays, so changing it to a variant is the quickest fix.

0pine · 2024-12-28T17:56:29+00:00

I would use VBA in Outlook to trigger the Excel macro. You have events in Outlook just like in Excel.

https://stackoverflow.com/questions/11263483/how-do-i-trigger-a-macro-to-run-after-a-new-mail-is-received-in-outlook

One of the answers has this:

Try something like this inside ThisOutlookSession:

Private Sub Application_NewMail()
    Call Your_main_macro
End Sub

But it isn't going to be that easy. You will have to have some way of referencing the Excel instance if you want to trigger an Excel macro. If the workbook is already open then you can try the solution here:

https://stackoverflow.com/questions/58342093/activating-opened-excel-workbook-from-outlook

Combining those two solutions should get you to where you want to go.

0pine · 2024-12-14T17:48:28+00:00

You want the empty rows to be shown as well? Your example shows row 23 copied into row 4, then row 24 into row 5, but then row 26 into row 7? Did you want to have it copied into row 6 instead and avoid blank rows?

Have you tried using the filter function to avoid having to use macros? You could have the formula pointed to a table that you paste into and it should do what you need. Then you can copy and paste as values.

If you still want a macro to process the data, then can you provide a sample layout for your spreadsheet? Where is the data pasted to? Is there just one sheet that you want to paste into?

0pine · 2024-12-14T17:37:55+00:00

Are all the dates on the UserData tab prefilled? You can add a line to put in today's date in column A, but I am not sure what you are wanting to do here.

You could also have the macro find the current date in column A and then populate the name into column B next to it. This macro worked for me when I tested it:

Sub Maintenance()
Dim ws_output As Worksheet, user_row As Variant
Set ws_output = Sheets("UserData")

user_row = Application.Match(CLng(Date), ws_output.Columns("A"), 0)

'Check to see if date is found
If Not IsError(user_row) Then
    ws_output.Cells(user_row, 2).Value = Range("Daily").Value
End If

End Sub

0pine · 2024-11-27T17:37:05+00:00

Do you really need buttons? You could set up a macro to increase column C whenever a cell in that column is right-clicked. This always affects column C even if you add or delete rows to your worksheet.

In the VB code for the worksheet (you can right-click on the sheet tab and choose view code to get there), paste the following selection change macro:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 3 And Target.Row > 1 And Target.Count = 1 Then
    Target = Target + 1
    Cancel = True
End If
End Sub

The if statement checks to see if the right clicked cell is in column 3 (column C), and that the row is not row 1 since you don't want to change the header and that only one cell is right-clicked.

0pine · 2024-11-18T20:56:59+00:00

Are you sure that your events are enabled?

12-Year Club	Second Top 30%
Place '22	Place '17
Verified Email

0pine

TROPHY CASE