Help with filtering

I have a large spreadsheet that has column A labeled as "Description" and column B labeled as "Temperature." The "Description" remains constant and "Temperature" changes daily.

I want to write a macro that will take the numerical value based on the cell under "Description" and move them both to a new sheet.

So, for example:
"Description" is Canada and "Temperature" is 58 for day one
then
"Description" is Canada and "Temperature" is 49 for day two
and so on...

The macro should move "Canada" and "58" then "Canada" and "49" to a new sheet and display them in Columns A and B.

Can anyone tell me how to begin writing this?

Thanks

Why not just have column A moved to ciolumn A on the new sheet, and B to B, then C and so on, that is not replicatijng Canada.

You could even have column headings saying which daya it is.

This little macro will move all the descriptions to sheet 2 along with their accompanying temperatures from column b, it uses sheet 3 to create a unique list and then goes through the list, then clears the list from sheet 3.
[VBA]
Sub Move_Temps()
Dim Rng As Range, MyCell As Range
Application.ScreenUpdating = False
Set Rng = Sheets("Sheet3").Range("A2:" & Range("A65536").End(xlUp).Address)
Sheets("Sheet1").Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns( _
"A:A"), CopyToRange:=Sheets("Sheet3").Range("A1"), Unique:=True
For Each MyCell In Rng
Sheets("Sheet1").Range("A2").AutoFilter Field:=1, Criteria1:=MyCell.Value
If MyCell.Value = "" Then GoTo Nxt
With Sheets("Sheet1").Range("A2:" & Range("B65536").End(xlUp).Address)
.Cells.SpecialCells(xlVisible).Copy Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
Application.CutCopyMode = False
End With
Nxt:
ActiveSheet.AutoFilterMode = False
Next
Sheets("Sheet3").Cells.ClearContents
Application.ScreenUpdating = True
End Sub
[/VBA]

Wonderful! That works great...

Ok, say I need to run a report and there are only few major areas that I want to show, such as "Canada" and "US" with their respective temperatures, and leave all other areas such as "Asia" out.

Would I change the "Criterial" to state the specific description?

Because I don't always need to capture all temperatures on the same report, I will need to discriminate between the descriptions and have only select areas pulled at certain times (I don't mind going in and creating specific macros for specific areas that I will be pulling most often if I need to).

Thanks for your help!

Why not send your data to a pivot table?

Perhaps you could add a column where you enter a number 1,2,3...etc and then run the macro using these as the criteria?, lets say column A now becomes the Index number column, now use the code below!
[vba]
Sub Move_Temps()
Dim Rng As Range, MyCell As Range
Dim strEntry As Variant
Application.ScreenUpdating = False
strEntry = InputBox("Enter Number(s) in this format x,x,xx?", "Index Report Selection")
Sheets("Sheet3").Range("A1").Value = strEntry
With Sheets("Sheet3").Range("A1")
.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,Comma:=True
End With
Set Rng = Sheets("Sheet3").Range("A1:" & Range("IV1").End(xlToLeft).Address)
For Each MyCell In Rng
Sheets("Sheet1").Range("A2").AutoFilter Field:=1, Criteria1:=MyCell.Value
If MyCell.Value = "" Then GoTo Nxt
With Sheets("Sheet1").Range("B2:" & Range("C65536").End(xlUp).Address)
.Cells.SpecialCells(xlVisible).Copy Destination:=Sheets("Sheet2"). _
Range("A65536").End(xlUp).Offset(1, 0)
Application.CutCopyMode = False
End With
Nxt:
ActiveSheet.AutoFilterMode = False
Next
Sheets("Sheet3").Cells.ClearContents
Application.ScreenUpdating = True
End Sub
[/vba]Edited to tidy code up 22:21

Quote:

---

Originally Posted by cmm0812

Wonderful! That works great...

Ok, say I need to run a report and there are only few major areas that I want to show, such as "Canada" and "US" with their respective temperatures, and leave all other areas such as "Asia" out.

Would I change the "Criterial" to state the specific description?

Because I don't always need to capture all temperatures on the same report, I will need to discriminate between the descriptions and have only select areas pulled at certain times (I don't mind going in and creating specific macros for specific areas that I will be pulling most often if I need to).

Thanks for your help!

---

Type in the areas you want seperated by a comma. Will be copied to sheet 2. Possible enhancements are :
1. clearing sheet2 before doing the copy
2. use a form with listbox to select multiple areas
2. apply some formatting
3. something else you can think off
[vba]Sub Selected_Areas()
'starting worksheet
Dim ws As Worksheet
'destination worksheet
Dim dest As Worksheet
'one item (region) to copy from array
'number in array starting from 0
Dim vItem As Long
'array holding all the regions
Dim vArea
'the string of the regions
Dim vShowSelection As String
'last row of sheet1
Dim lrow As Long
Set ws = Worksheets(1)
Set dest = Worksheets(2)
lrow = ws.Range("A" & Rows.Count).End(xlUp).Row
'copy the headers
ws.Range("A1:B1").Copy dest.Range("A1")
'Canada, US or US, Asia or whatever combination you want
'but ending with NO ,
vShowSelection = Application.InputBox("Give areas (A, B, C)" & _
vbCrLf & "seperated by a comma ...", "Select areas to get ...")
vArea = Split(vShowSelection, ",")
'loop through the array starting from 0
For vItem = LBound(vArea) To UBound(vArea)
ws.Range("A1").AutoFilter field:=1, Criteria1:=vArea(vItem)
ws.Range("A2:B" & lrow).SpecialCells(xlCellTypeVisible).Copy _
dest.Range("A" & dest.Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0).Row)
Next vItem
ws.Range("A1").AutoFilter
dest.Columns.AutoFit
End Sub[/vba]Charlize