Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Data from Dataset
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Data from Dataset
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Data from Dataset
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to extract a dataset to the active cell? | Excel Programming | |||
Charting only partial data from a large dataset | Charts and Charting in Excel | |||
Sorting dataset automatically after entering data in a row [or cel | Excel Discussion (Misc queries) | |||
assign dataset to matching dataset | Excel Programming | |||
Converting A Quarterly Dataset to Weekly Dataset | Excel Programming |