Copying quote rows from monthly sheets to cancellation sheet if criteria is met

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,589
I think I can remember changing that code reference from f1/h1 to b25/b27 when I was at work today. I will upload another version of the workbook when I am next at work, which is Friday.

I know you didn't write that code that won't work, I was trying to write some code but I got it all wrong.

With the code in post 68, the reason I took out the select case with all of the monthly sheets was that I thought since, now the data is only coming from one sheet, you only need to reference that sheet.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
999
Hello Dp,

In the latest sample you have supplied, you have the destination sheet variable 'sh' assigned the value of Sheets("Totals") hence, instead of the data from the monthly source sheets being transferred to the "Cancellations" sheet, it is being transferred to the input sheet "Totals" and literally getting lost amongst the data that is being used for your chart.
Excel won't allow the same variable for two worksheets so you need to declare another variable for the destination sheet. Following is the code again amended to show the change:-

VBA Code:
Option Explicit
Sub Test()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet
        Set sh = Sheets("Totals")
        Set sht = Sheets("Cancellations")
        Dim Req As String: Req = sh.[B25].Value
        Dim Dt As String: Dt = sh.[B27].Value
        
Application.ScreenUpdating = False
        
        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, Dt
                                .AutoFilter 3, Req
                                .Offset(1).EntireRow.Copy sht.Range("A" & Rows.Count).End(3)(2)
                                .Offset(1).EntireRow.Delete
                                .AutoFilter
                        End With
                End If
        Next ws
        
sh.Range("B25,B27").ClearContents
Application.ScreenUpdating = True

End Sub
You also need to change this line:-

VBA Code:
sh.Range("F1,H1").ClearContents
to
VBA Code:
sh.Range("B25,B27").ClearContents
just as I have done for you in the code above.

You'll note I've added the new variable 'sht' as the destination sheet (Cancellations") as well.

I've tested it in your sample and the code works as it should.

Cheerio,
vcoolio.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,589

ADVERTISEMENT

I just noticed, it is not sorting on the cancellations sheet by date, even though I have the SortCells sub run every time the sheet is changed.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,589
My sort code is working but whenever I enter in a request number in B25 of the totals sheet and a date in B27, the numbers are copied to the cancellations sheet but the cancellations sheet is not sorted.

This is my code on the totals sheet
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B27")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Call Test
Call SortCells
End Sub
After I transfer the cancelled job, it is transferred to the cancellations sheet and the sheet is not sorted, I go to the vba editor and run SortCells manually and that sorts the sheet.

Why won't it run after I enter the date in B27?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,589

ADVERTISEMENT

I put a button on the cancellations sheet which called the SortCells sub and that works.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
999
Hello Dp,

Why won't it run after I enter the date in B27?

I noticed that, in your SortCells code, you have commented out the With statement (green font):-

VBA Code:
Sub SortCells()
'With Sheets("Cancellations")
    Range("A4", Range("O" & Rows.Count).End(xlDown).Address).Sort _
    Key1:=Range("A4"), Order1:=xlAscending
'End With

End Sub
Hence, the code is not fully qualified in reference to the sheet that it needs to work on. It is now assuming that it needs to work on the "Totals" sheet as it is being called from within the "Totals" sheet module. Remove the apostrophes from the with statement and it should work on the "Cancellations" sheet or, if you are happy with the button on the "Cancellations" sheet, then leave it as is.

It would be a better option to fully qualify the SortCells code as follows:-

VBA Code:
Sub SortCells()

Dim ws As Worksheet: Set ws = Sheets("Cancellations")

    ws.Range("A4", ws.Range("O" & ws.Rows.Count).End(xlDown).Address).Sort _
    Key1:=ws.Range("A4"), Order1:=xlAscending

End Sub
I hope that this helps.

Cheerio,
vcoolio.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,589
Thanks for your help vcoolio. I initially tried to remove the quote marks after I asked the question and now at this stage, the result of what I was getting is what I need.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,101
Messages
5,509,244
Members
408,717
Latest member
Bea2136

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top