Some help with Autofilter

[jgalas]

Hi everyone,
I have a sheet ("Registos") with data and i want to sort them with criteria in orther sheet (PrintDupla") based on cell Q13 and BF2. It will returm one unique row of data.
I want to copy this row to the first row of data sheet.

I have the folow code but data dont refresh when criteria change.

any help

Sub Filtro()
'
' Macro2 Macro
'

'
    Sheets("Registos").Activate
    Range("A4:AE4").Select
    Selection.autofilter
    ActiveSheet.Range("$A$4:$AE$9").autofilter Field:=1, Criteria1:="=" & Sheets("PrintDupla").Range("BF2").Value
    ActiveSheet.Range("$A$4:$AE$9").autofilter Field:=2, Criteria1:="=" & Sheets("PrintDupla").Range("Q13").Value
    Range("A6:AE6").Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste
End Sub

 

[jgalas]

Anyone.....

 

[Trebor76]

Hi jgalas,

Which column of the 'Registos' tab is to be filtered by cell Q13 of the 'PrintDupla' tab and the same for cell BF2 from the 'PrintDupla' tab?

Robert

 

[gregtx81]

The autofilter object filters by hiding rows-- so when you copy you only want to copy the visible cells. Ignoring the header (in row 4), try:

Range("A5:AE9").SpecialCells(xlVisible).Copy Destination:=Range("A1")

 

[jgalas]

Thanks guys for your replys.

I have another problem....
I want to copy every row of sheets ("RegDupla") to the last aviable row of sheet("Registos") in this way:

Sheets (RegDupla).range("D1") to sheets(Registos).range(B#)
Sheets (RegDupla).range("D2") to sheets(Registos).range(AC#)
Sheets (RegDupla).range("I2") to sheets(Registos).range(AD#)
Sheets (RegDupla).range("B#") to sheets(Registos).range(A#)
Sheets (RegDupla).range("D#:AC#") to sheets(Registos).range(C#:AB#)

# it's the number of the row... in sheets(RegDupla) can be 10 for exemple and 1254 in the sheets(Registos).

Can you help me on this please

 

[Trebor76]

Hi jgalas,

Though you should really start a new thread for each separate question, try this:

Option Explicit
Sub Macro1()

    Dim lngCopyRow As Long, _
        lngPasteRow As Long
        
    Application.ScreenUpdating = False
        
    'Copy cell D1 from the 'Registos' tab to the next available row _
    in column B of the 'RegDupla' tab.
    lngPasteRow = Sheets("Registos").Cells(Rows.Count, "B").End(xlUp).Row + 1
    Sheets("RegDupla").Range("D1").Copy _
        Sheets("Registos").Range("B" & lngPasteRow)
    
    'Copy cell D2 from the 'Registos' tab to the next available row _
    in column AC of the 'RegDupla' tab.
    lngPasteRow = Sheets("Registos").Cells(Rows.Count, "AC").End(xlUp).Row + 1
    Sheets("RegDupla").Range("D2").Copy _
        Sheets("Registos").Range("AC" & lngPasteRow)
    
    'Copy cell I2 from the 'Registos' tab to the next available row _
    in column AD of the 'RegDupla' tab.
    lngPasteRow = Sheets("Registos").Cells(Rows.Count, "AD").End(xlUp).Row + 1
    Sheets("RegDupla").Range("I2").Copy _
        Sheets("Registos").Range("AD" & lngPasteRow)
    
    'Copy the last row in column B from the 'Registos' tab to the next available row _
    in column A of the 'RegDupla' tab.
    lngCopyRow = Sheets("RegDupla").Cells(Rows.Count, "B").End(xlUp).Row
    lngPasteRow = Sheets("Registos").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Sheets("RegDupla").Range("B" & lngCopyRow).Copy _
        Sheets("Registos").Range("A" & lngPasteRow)
        
    'Copy the last row in columns D to AC from the 'Registos' tab to the next available row _
    in columns C to AB of the 'RegDupla' tab.
    lngCopyRow = Sheets("RegDupla").Range("D:AC").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lngPasteRow = Sheets("Registos").Range("D:AC").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Sheets("RegDupla").Range("D" & lngCopyRow & ":AC" & lngCopyRow).Copy _
        Sheets("Registos").Range("C" & lngPasteRow & ":AB" & lngPasteRow)
        
    Application.ScreenUpdating = True

End Sub

HTH

Robert

 

[jgalas]

Hi Robert thanks again for the reply.
I have iniciate a new thread for this last question.
I have trying your code but it dont work like i want.
Your code create 2 row of data and what i want is all data in one row.

Can you look at http://www.mrexcel.com/forum/showthread.php?t=583284
Please

 

[Trebor76]

Try this :

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long, _
        lngLinkRow As Long
        
    Application.ScreenUpdating = False
        
    'Link the value from cell D1 from the 'RegDupla' tab to _
    the last row in column B of the 'Registos' tab.
    lngLinkRow = Sheets("Registos").Cells(Rows.Count, "B").End(xlUp).Row
    Sheets("RegDupla").Range("D1").Value = Sheets("Registos").Range("B" & lngLinkRow).Value
    
    'Link the value from cell D2 from the 'RegDupla' tab to _
    the last row in column AC of the 'Registos' tab.
    lngLinkRow = Sheets("Registos").Cells(Rows.Count, "AC").End(xlUp).Row
    Sheets("RegDupla").Range("D2").Value = Sheets("Registos").Range("AC" & lngLinkRow).Value
    
    'Link the value from cell I2 from the 'RegDupla' tab to _
    the last row in column AD of the 'Registos' tab.
    lngLinkRow = Sheets("Registos").Cells(Rows.Count, "AD").End(xlUp).Row
    Sheets("RegDupla").Range("I2").Value = Sheets("Registos").Range("AD" & lngLinkRow).Value
    
    'Link the value from the last row in column B of the 'RegDupla' tab to _
    the last row in column A of the 'Registos' tab.
    lngLastRow = Sheets("RegDupla").Cells(Rows.Count, "B").End(xlUp).Row
    lngLinkRow = Sheets("Registos").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("RegDupla").Range("B" & lngLastRow).Value = Sheets("Registos").Range("A" & lngLinkRow)
        
    'Link the values from the last row in from columns D to AC (inclusive) of the 'RegDupla' tab to _
    the last row in columns C to AB (inclusive) of the 'Registos' tab.
    lngLastRow = Sheets("RegDupla").Range("D:AC").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lngLinkRow = Sheets("Registos").Range("D:AC").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("RegDupla").Range("D" & lngLastRow & ":AC" & lngLastRow).Value = Sheets("Registos").Range("C" & lngLinkRow & ":AB" & lngLinkRow).Value
        
    Application.ScreenUpdating = True

End Sub

 

[jgalas]

Try this :

Option Explicit
Sub Macro1()
 
    Dim lngLastRow As Long, _
        lngLinkRow As Long
 
    Application.ScreenUpdating = False
 
    'Link the value from cell D1 from the 'RegDupla' tab to _
    the last row in column B of the 'Registos' tab.
    lngLinkRow = Sheets("Registos").Cells(Rows.Count, "B").End(xlUp).Row
    Sheets("RegDupla").Range("D1").Value = Sheets("Registos").Range("B" & lngLinkRow).Value
 
    'Link the value from cell D2 from the 'RegDupla' tab to _
    the last row in column AC of the 'Registos' tab.
    lngLinkRow = Sheets("Registos").Cells(Rows.Count, "AC").End(xlUp).Row
    Sheets("RegDupla").Range("D2").Value = Sheets("Registos").Range("AC" & lngLinkRow).Value
 
    'Link the value from cell I2 from the 'RegDupla' tab to _
    the last row in column AD of the 'Registos' tab.
    lngLinkRow = Sheets("Registos").Cells(Rows.Count, "AD").End(xlUp).Row
    Sheets("RegDupla").Range("I2").Value = Sheets("Registos").Range("AD" & lngLinkRow).Value
 
    'Link the value from the last row in column B of the 'RegDupla' tab to _
    the last row in column A of the 'Registos' tab.
    lngLastRow = Sheets("RegDupla").Cells(Rows.Count, "B").End(xlUp).Row
    lngLinkRow = Sheets("Registos").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("RegDupla").Range("B" & lngLastRow).Value = Sheets("Registos").Range("A" & lngLinkRow)
 
    'Link the values from the last row in from columns D to AC (inclusive) of the 'RegDupla' tab to _
    the last row in columns C to AB (inclusive) of the 'Registos' tab.
    lngLastRow = Sheets("RegDupla").Range("D:AC").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lngLinkRow = Sheets("Registos").Range("D:AC").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("RegDupla").Range("D" & lngLastRow & ":AC" & lngLastRow).Value = Sheets("Registos").Range("C" & lngLinkRow & ":AB" & lngLinkRow).Value
 
    Application.ScreenUpdating = True
 
End Sub

Hi Robert,
I used your above code but nothing happened ...
The name of the sheets is correct, the cells also ... but nothing

 

[Trebor76]

No idea sorry, as it worked for me

I take it there is data to be linked?