Need two macros to be have in the same way

[Cele]

Hi
I was wondering of someone can help me fix the macros for "Move From Yellow Tab" and "Move From Purple Tab" Located on the very last tab called "Invoice Tab".

I want to be able to filter by "confirmed" and by "Date (confirm or order)" on both the Renewal Report and Transactional Report tabs. And be able to copy and paste whatever i filter when doing this.

I want this macro on the Invoice Audit tab to behave the same way as the one on the “Cancellations Report” tab. That when I filter the confirms on the Renewal Report tab by confirm, on any particular date(s), it copies and pastes it to the Invoice Audit tab.

See attachment for clarifications...

Can some help me please? And thanks.

 

[Luke M]

Hi Cele,

Please check out the attached, and the changes I made to the two macros.

 

[Cele]

Almost there...question when copying from yellow tab to black tab, everything is copying perfectly. Except column M when copying from yellow to black tab. Can it not copy those numbers when copying from yellow to black tabs only? Please and thanks in advance

 

[Luke M]

Sure thing. Can tweak the resize code to be 3 columns, rather than 4.

 

[Cele]

The macro for the tab called "Cancellations Report" isn't working. An error comes up, can you help? Please and thanks.

 

[Cele]

I need it to copy and paste when filter all the items in yellow tab by status "Cancelled". Copy and paste all cancels to the maroon tab. Columns A - Q that are cancelled.

 

[Cele]

Please see attachments below to see what i mean. Thanks.

 

[Cele]

the only thing i was unable to fix was the date on column I on the maroon tab, can you help please? Its supposed to look like a regular date MM-DD-YY. See this new attachment instead.

 

[Luke M]

Maroon tab is currently blank.

If I manually type a date, seems to show up just fine.


If you want to change the format, can select all of col I and change cells' number format to be
dd-mm-yy

 

[Cele]

See attached below.
The problem lies on this portion of the macro
wsIA.Range("I" & nr).PasteSpecial xlPasteValues
If column O on the maroon tab is copying correctly, why isnt column I doing this correct?

 

[Luke M]

Which macro? Also, note that you have three macros of name:
MoveFrom___Tab, but there are only 2 buttons on the Invoice Audit sheet...I think you need to clarify which macros are doing what, and which sheets are involved.

Is the 'MoveFromMaroonTab' macro misnamed, and is actually just copying Cancelled items from the yellow sheet?

 

[Cele]

Thats what the MoveFromMaroonTab is supposed to do. Copy Cancelled items from the yellow sheet. Thats the only one giving me a problem. Its copying everything except the formatting of that date on column I. Is there any way to fix the macro so it copies the date on column I properly?

 

[Luke M]

Since the first copy line already brings everything over...

Sub MoveFromMaroonTab()
    Dim wsRenewal   As Worksheet, _
        wsIA        As Worksheet, _
        nr          As Long
   
    Set wsRenewal = Worksheets("Renewal Report")
    Set wsIA = Worksheets("Cancellations Report")
   
    nr = wsIA.Columns(1).Find(What:="*", After:=wsIA.Range("a1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                                    SearchDirection:=xlPrevious, MatchCase:=False).Row + 1
    On Error GoTo errTrap
   
    Application.ScreenUpdating = False
    'Start with the range that is filtered
    With wsRenewal.AutoFilter.Range
        'Copy values from each relevant section
        .Offset(1, 0).Resize(.Rows.Count - 1, 17).SpecialCells(xlCellTypeVisible).Copy
            wsIA.Range("A" & nr).PasteSpecial xlPasteValues
        'Don't need to do a second copy
'        .Offset(1, 9).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Copy
'            wsIA.Range("I" & nr).PasteSpecial xlPasteValues
    End With
    'Clear the clipboard
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
Exit Sub
errTrap:
MsgBox "There is no filter set!"

End Sub

 

[Cele]

That actually worked. Thank you. One last thing...please take a look at the last tab on the far right. On the attachment below. Can you help me with that macro so it copies, when I filter the confirmed? tab on the yellow tab to show n or y and paste it on the blue tab, on the order shown below

Yellow Tab (Renewal Report)Blue Tab (Report - All)
DA
EB
GC
FD
CE
HF
QG
JH
II

 

[Luke M]

Do you need the macro to apply the filter, or will you have already manually applied the filter you want? Currently, the code works in the latter method.

I don't know what you mean about the sort...is this a particular column you want sorted? If yes, which one? None of your sample data matches what you posted.