[[email protected]]

I'm looking to extract data that meets a certain criteria but not sure how to do it via a macro. What I'm after:-

Workbook1= Dataset
Workbook2 = Where it will be extracted to (copied not cut)
I will copy rows 1-12 as headers first
Based on a value I input in a Dialogue box, this is the data that will be extracted to new Workbook2
The value input above will reside in Column A (starting at Row 13) in Workbook1
Once matched I will copy all the Row
The match/copy process will continue until it hits the first blank cell in Column A, signifying the end of the Dataset
Then Copying the column widths in Workbook1 to match Workbook2

I hope above makes sense

[Mandeep Baluja]

check this out !! Name your sheet as Dataset which contains data and name another sheet as output sheet.


Sub CopyRows()

Dim ws As Worksheet
Dim wsout As Worksheet
Dim lr As Long

Set ws = ActiveWorkbook.Sheets("dataset") 'dataset sheet
Set wsout = ActiveWorkbook.Sheets("output") 'output sheet

'Getting values in this variable
tempval = InputBox("Enter the value")

'Copying first 12 rows
ws.Rows("1:12").Copy Destination:=wsout.Range("A1")

'find last row from your Dataset sheet
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

'Filter all rows matching criteria You can adjust your columns by changing F to your defined column
ws.Range("A12:F" & lr).AutoFilter field:=1, Criteria1:=tempval
ws.Range("A12:F" & lr).SpecialCells(xlCellTypeVisible).Copy Destination:=wsout.Range("A12")

'turn off filter mode
ActiveSheet.AutoFilterMode = False

End Sub

Regards,
Mandeep baluja

[[email protected]]

Hey, that works pretty good, thanks, some things I'd like to change

-Instead of specifying the originating sheet name, can the code just work from the 'active sheet'?
-Instead of specifying the destination sheet in the same workbook, can it auto create a new workbook
-Can you format the 'new workbook' sheet in the same format as 'originating workbook' sheet