How to import data based on a variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a basic spreadsheet and need to import data from that sheet into a new
sheet, based on criteria from one colum in the original sheet. Can't figure
out a formula for this.

Sheet One (Printing press waste calculator)

col1 col2 col3 col4 col5 col6
col7 col8 col9
date job operator ordered start # stop # run
var -+ var %

6-6-06 test Dan 5,000 415250 420450 5200
200 4%
6-6-06 test Sam 5,000 415250 420450 5200
200 4%
6-6-06 test Ed 5,000 415250 420450 5200
200 4%
6-6-06 test Dan 5,000 415250 420450 5200
200 4%
6-6-06 test Ed 5,000 415250 420450 5200
200 4%
6-6-06 test Sam 5,000 415250 420450 5200
200 4%

I need a new sheet for each operator, importing all rows of data, but only
by the operator's name. So, the resulting worksheet will show everything
(like above) but will only show data for a particular operator.



I need the new sheet to import all data
 
Hi, why not to use a auto filter?

does it helps?
regards from Brazil
Marcelo

"kjp55" escreveu:
 
I could but can I still get the totals rows at the bottom to appear somehow?
When I tried the autofilter, the last line, which is grand totals,
disappeared and I couldn't make it apear until I took the filter off.
 
try to create a subtotal one row after the end of the data.

does it helps?
Marcelo

"kjp" escreveu:
 
Yes, that worked but it still shows the totals for the entire columns, all
records. Is there a formula to make it only calculate and show a sub-total
of the filtered records, instead of showing the sum of 'all' records? And
then still having the master total of all records at the bottom when 'all
records' is selected in the filter?

Thanks for your patience.
 
You will not be able to find a formula, but I build a subroutine that you can
use to get you started. it assumes 9 colums, I used your example as a
reference. The only thing I added was naming the sheet where the base data is
as "Source".

Cheers,


Sub Macro1()
Dim iLastOp As Integer
Dim wks As Worksheet
Dim wksdest As Worksheet
Dim rngOperat As Range
Dim rngFiltOperat As Range
Dim Scell As Range
Dim Wkname As String

ThisWorkbook.Application.ScreenUpdating = False
iLastOp = Range("C65536").End(xlUp).Row
Set wks = ThisWorkbook.Worksheets("Source")
wks.Range("K:K").ClearContents
wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("K1" _
), Unique:=True
iLastOp = wks.Range("K65536").End(xlUp).Row

Set rngOperat = Range("K2:K" & iLastOp)

For Each rngOperat In rngOperat
ThisWorkbook.Worksheets.Add
Worksheets(1).Name = rngOperat
Next rngOperat
Set rngFiltOperat = wks.Range("K2:K" & iLastOp)
wks.Select

wks.Columns("C:C").AutoFilter
For Each rngFiltOperat In rngFiltOperat
wks.Select
wks.Range("C65536").End(xlUp).Select

With Selection
.AutoFilter Field:=1, Criteria1:=rngFiltOperat
End With
Wkname = rngFiltOperat
Set wksdest = ThisWorkbook.Worksheets(Wkname)
Cells.Copy
wksdest.Paste
wksdest.Range("K:K").ClearContents
Next rngFiltOperat
wks.Select
wks.Range("C:C").AutoFilter
wks.Range("K:K").ClearContents
ThisWorkbook.Application.ScreenUpdating = True
End Sub
 
try to use

subtotal(9,g2:g90)

adjust the range as need.

does that help?
regards from Brazil
Marcelo

"kjp" escreveu:
 
Thanks. I added the sub to the worksheet and got a compilation error all
highlighted in red when I ran the compiler)...

[this line is highlighted as incorrect by the compiler]
wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy,

CopyToRange:=Range("K1" _
), Unique:=True .....

seems this statement is not valid. As I know little about Macros and
subroutines, I'm not sure what I'm looking at. Do I need to change
something to make it work?
 
The only reason why it would stop is if the destination cell in my example
K1, is not empty. Make sure the K column is clear of any data or change it to
a column where there will never be any data.
Range("Z1".....

One more thing: I did not use the col1...Col8 reference I modified the
example so that the headers would be:
date job operator ordered start # stop # run var -+
var %




kjp said:
Thanks. I added the sub to the worksheet and got a compilation error all
highlighted in red when I ran the compiler)...

[this line is highlighted as incorrect by the compiler]
wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy,

CopyToRange:=Range("K1" _
), Unique:=True .....

seems this statement is not valid. As I know little about Macros and
subroutines, I'm not sure what I'm looking at. Do I need to change
something to make it work?


Michael said:
You will not be able to find a formula, but I build a subroutine that you
can
use to get you started. it assumes 9 colums, I used your example as a
reference. The only thing I added was naming the sheet where the base data
is
as "Source".

Cheers,


Sub Macro1()
Dim iLastOp As Integer
Dim wks As Worksheet
Dim wksdest As Worksheet
Dim rngOperat As Range
Dim rngFiltOperat As Range
Dim Scell As Range
Dim Wkname As String

ThisWorkbook.Application.ScreenUpdating = False
iLastOp = Range("C65536").End(xlUp).Row
Set wks = ThisWorkbook.Worksheets("Source")
wks.Range("K:K").ClearContents
wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("K1" _
), Unique:=True
iLastOp = wks.Range("K65536").End(xlUp).Row

Set rngOperat = Range("K2:K" & iLastOp)

For Each rngOperat In rngOperat
ThisWorkbook.Worksheets.Add
Worksheets(1).Name = rngOperat
Next rngOperat
Set rngFiltOperat = wks.Range("K2:K" & iLastOp)
wks.Select

wks.Columns("C:C").AutoFilter
For Each rngFiltOperat In rngFiltOperat
wks.Select
wks.Range("C65536").End(xlUp).Select

With Selection
.AutoFilter Field:=1, Criteria1:=rngFiltOperat
End With
Wkname = rngFiltOperat
Set wksdest = ThisWorkbook.Worksheets(Wkname)
Cells.Copy
wksdest.Paste
wksdest.Range("K:K").ClearContents
Next rngFiltOperat
wks.Select
wks.Range("C:C").AutoFilter
wks.Range("K:K").ClearContents
ThisWorkbook.Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
 
Back
Top