This is clearly an extension of the original request.
May be best to start a new thread and also include a sample spreadsheet with a mock-up of the final result you are trying to achieve.
This is clearly an extension of the original request.
May be best to start a new thread and also include a sample spreadsheet with a mock-up of the final result you are trying to achieve.
Roy is, as always, correct !
AutoFilter is a much faster way to do this type of copying between sheets.
If you want to use that instead of looping, revised version using AutoFilter is attached.
The loop in the NewCopy macro that has the "Like" statements in it is created as a series of "ElseIf"s.
This means that, for example, a Call Month value of "December / April / August" will only copy to April because April gets tested before August or December.
If you want to copy data for a Call Month value of "December / April / August" to each of the December, April & August sheets then the loop needs to be redesigned. The attached version does this. Bear in mind that this loop now takes a longer to run and it has to test each row 12 times for each month, not skip to the next row like it did before when one of the ElseIf conditions was met.
There's a bunch of ways to do this. One way is in the attached revised copy of your original workbook.
I made a few other changes as well to streamline the code a bit.
I also added a Clear Data button to the Main sheet if you want the option to do this in one go - as the name implies this will clear the data from all the "Month" sheets (except the header row).
Thanks !
You're welcome.
The dynamic range called Teachers becomes invalid when you delete cell C3 since it is tied to that cell.
Maybe change the Worksheet_SelectionChange code as follows to reset that range when you delete in that column:
'Move up teachers
Application.EnableEvents = False
Rng.Delete Shift:=xlUp
'Reset the dynamic range
ActiveWorkbook.Names("Teachers").RefersTo = "=OFFSET(Teachers!$C$3, 0, 0, COUNTA(Teachers!$C:$C))"
Application.EnableEvents = True
Also best to add the EnableEvents False/True code otherwise you'll trigger the Worksheet_Change event whenever you delete a cell.
To do what I think you are trying to do, maybe move the Exit For:
You are welcome. Hopefully this will move you forward.
The way the code is currently written, it is only looking forward at any point in time ie, it is not looking at the whole set of data to determine the count for each row of data. This is why any countif code takes so long to run.
Your code currently provides accurate results if the rows of matching data are sequential eg rows 1935 and 1936.
This code would probably work as you require it if the data is sorted first, assuming data sorting does not interfere with anything else you may be doing.
You will also speed up processing by adding the following at the start of the macro
With Application
.ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
End With
and the corresponding code at the end of the macro:
I suspect COUNTIF is not providing the correct answers because you have a mixture of numbers and numbers stored as text.
You should be able to work around this by forcing a formatted count. As an excel formula, this would look something like:
One way to do this in VBA is something like:
Sub AltCountIf()
Dim x, y
Dim i As Long
With Application
.ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
End With
With Sheet1.Cells(1).CurrentRegion
'assume column A contains the data values to be counted
x = .Columns(1).Value
'assume column B will hold the results
y = .Columns(2).Value 'don't really need values, will just initialise y
'skip the header row and loop through to do the countifs
For i = 2 To UBound(x, 1)
y(i, 1) = Application.CountIf(.Columns(1), Format(x(i, 1), "#"))
Next
'write the results back to column B
.Columns(2).Value = y
End With
With Application
.ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
End With
MsgBox "Completed"
End Sub
Display More
This loads the entire dataset to be counted into an array and then places the countif values into a second array.
It may be more efficient than other types of loops however with nearly 350,000 rows this is still going to be very slow.
Also, loading all the data into arrays first may be more efficient but it will be memory intensive.
If you want to test it first maybe try using a smaller loop eg:
The error seems to be occurring in the C:\Windows\System32 folder which is (or can be) a protected Windows folder. You may need special permissions to safe files in this folder.
Does the error occur if you save the file in a non Windows protected folder?
Glad it worked for you.
You're welcome, if you're having date issues (particularly in VBA) it is most likely a date has been interpreted as mm dd yy.
Excel has a habit of interpreting dates as mm dd yy (even if that is not your local date setting).
You generally need to code around it, maybe try:
Private Sub CbDuration_Change()
If CbDuration.Value = "28 Days" Then
TxReview.Value = Format(DateAdd("d", 28, TxDate.Value), "dd mmm yy")
End If
If CbDuration.Value = "2 Months" Then
TxReview.Value = Format(DateAdd("m", 2, TxDate.Value), "dd mmm yy")
End If
If CbDuration.Value = "3 Months" Then
TxReview.Value = Format(DateAdd("m", 3, TxDate.Value), "dd mmm yy")
End If
TxReview_AfterUpdate
End Sub
Display More
You are welcome...all the best
When using an array with RemoveDuplicates, it needs to be a zero based integer array.
In the example file you posted there are only 11 columns, so the code snippet for this example would be:
Dim ColumnNumbers As Variant
ReDim ColumnNumbers(0 To 7)
ColumnNumbers = Array(1, 2, 3, 4, 6, 9, 10, 11)
Set rngDataRange = ActiveSheet.Cells(1, 1).CurrentRegion
With rngDataRange
Select Case .Columns.Count
Case 11
.RemoveDuplicates Columns:=(ColumnNumbers), Header:=xlYes
End Select
End With
Display More
Obviously, you can expand this if your actual workbook has more columns but the principal will be the same.