Posts by Mirinjawbro

Hello all

i have data in the 12 months of the year.

when i choose the month from the dropdown i need to to calculate the running total . this is the column after December.

A B C

JAN FEB MAR etc (dropdown - MARCH)

50 50 100 200

thanks

update ------ the code attached works fine, it goes through all locations and saves as needed

however these 150 locations (files) now have to be sent to the managers. we do not want them to be able to select the dropdown in C2 once its been saved and sent. (so they can see other location cash)

i can get the dropdown to disable but then the code stops running through as it is set to run through the dropdown in C2 until the end

all i need is once its saved to disallow the dropdown in C2

thanks

Sub save_all()

Application.DisplayAlerts = False

Dim dv As Validation
Dim r As Range, r1 As Range
Dim s As String, v As Variant
Dim cell As Range, i As Long, l As Long

Worksheets("tronc tool").Select

Set r = Range("C2")

On Error Resume Next

Set dv = r.Validation
l = dv.Type

On Error GoTo 0
If l <> xlValidateList Then
MsgBox "No Data Validation in cell " & r.Address & " or " & vbNewLine & _

"Data Validation in cell " & r.Address & " is not of the list type" & _
vbNewLine & "Processing is halted. Click OK to continue. . .", vbCritical

Exit Sub

End If

s = dv.Formula1

If InStr(1, s, "=", vbTextCompare) = 1 Then

s = Right(s, Len(s) - 1)

On Error Resume Next

Set r1 = Application.Range(s)

On Error GoTo 0

If r1 Is Nothing Then

MsgBox "Can't get range of source for data validation"

Else

For Each cell In r1

r.Value = cell.Value

End If

End If
Application.DisplayAlerts = True

End Sub

(all that needs to be added is a save location of choice and also as it saves to disallow the selection of dropdown in C2)

thanks so much

Hello all

i have a workbook with 150 different locations, these locations are in a dropdown list. as you select locations the table below changes showing the wages etc (works using lookups)

i need a macro that will

- move through the workbooks 1-150 and save them in set location

- MUST disallow dropdown in C2 after saving - wages etc are shown in the table once the location is selected .

currently these are all on 1 workbook.

need 150 workbooks

thanks

yes the company position isnt a perfect match like the example i gave.

AAA could be in different rows, as they all could so it works perfect

i really got to learn this formula. very very useful

thanks all

i think it worked

crazy formula

thanks so much

im not sure what you mean.

Jan - Dec can be labelled as 1-12 if needed.

so for example

first line of data

AAA live date is 12/04. as this is on the 12th day of the month, i will only be summing the 5th-12th month

if AAA live date was before the 10th day of the month i will sum all months including that month.

Hello

please see file attached

need to sum the profit for that year using company and month.

hard part is only sum that month based on the live date column, if the date is before the 10th you count that month into the totals

thanks

thanks alot for that

never used power query so will try and work out from the code above the steps you took.

thanks

attached example

the bit im struggling with is to get the number of lines it has in total and then paste the relevant headers that many times

hopefully the attachment is better

thanks

thankyou very much, will give it a go.

this example there are 8 lines so it pastes the group and term 8 times with the relevant data

there could be 3000 lines, so it would need to count to 3000 and paste the group and term 3000 times with the data

hopefully ive explained better

Hello

each week a spreadsheet of numbers is uploaded. the list length varies but the terms and conditions do not. (red at the top)

i have made the macro copy column a2 down to the end and paste into a seperate tab, then go to b2 and then paste it underneath and so on. thats working and fine

what i now need is too add 1+23 from A1 the correct number of times to the right of A2 downwards.

then take 1+23 in B1 and add it to the correct number of lines in B2 downwards

so , lets assume this has 100 rows

column A needs to be A2 down to A101 and next to this 1+23 100 times

if the list was 87 long, id need A2-88 and 1+23 next to it 87 times

then it goes to the next coulmn and repeats

thanks

Hello all

need help on the below please

i need a macro that stops running once a certain date has passed

so for example.

A1 has = today()

if A1 > "10/03/2021" stop macro

thanks