Data Extraction (without autofilter)

[jaypatel]

Hi all,

I have 2 worksheets: Extraction and Dataset.

On the extraction sheet (where I want the data to be presented), if cell A1= "7500-2018", I want to extract from the dataset worksheet anything with 7500-2018 in column A. Within Dataset, I would want columns A to L copied across, if Extraction!A1=Dataset!A:A....

The reason why I don't wish to have autofilter, it contains salary information which I do not want the user to see.

Many thanks in advance

Jay

 

[PCL]

"if Extraction!A1=Dataset!A:A...." can you give more details !!

 

[PCL]

Is there formulas, format to copy or just the values ?

 

[PCL]

For the first part of the specification try

Option Explicit
Sub Copy()
Const DstWsName As String = "Extraction"
Const OrgWsName As String = "Dataset"
Const WkCol As String = "A"
Dim I As Integer, LR As Integer
Dim WkKey  As String
Dim OrgWs As Worksheet, DstWs As Worksheet


    Set OrgWs = Sheets(OrgWsName)
    Set DstWs = Sheets(DstWsName)
    WkKey = DstWs.Cells(1, WkCol)
    With OrgWs
        LR = .Cells(Rows.Count, WkCol).End(3).Row
        For I = 1 To LR
            If (.Cells(I, WkCol) = WkKey) Then
                .Rows(I).Copy DstWs.Cells(Rows.Count, 1).End(3)(2)
            End If
        Next
    End With
End Sub

 

[jaypatel]

Hi

it is just values.... no formulaes...

Thanks

Jay

 

[jaypatel]

"if Extraction!A1=Dataset!A:A...." can you give more details !!

okay, so if cell a1 on dataset = cell a1 on extraction, then copy A1:L1

if cell a2 on dataset does not equal a1 on extraction, then do not copy A2:L2
if cell a3 on dataset = cell a1 on extraction, then copy A3:L3

hope this makes sense....

thanks

 

[PCL]

OKAY, so the code sent is enough ...!