Automatically copying data from worksheet to worksheet

G

GazMo

I have a main worksheet that contains all my data (Columns I thru Z) -
want to now automatically copy/sort this information by country (Colum
K) to individual worksheets. (eg) if in 'K3' it has a country o
Austria then all this lines data (I thru Z) is copied into the Austri
worksheet. Can this be done without using a macro, I would prefer t
use formulas in each worksheet?

Any assistance would be great thanks
 
S

StephenL

Try VLOOKUP(A1,'Country List'!$A$4:$Z$169,4), A1 is the cell referenc
whereby you type in the country name,and Country List ia the sheet tha
contains your data, A4:Z169 is your range of data, and the number afte
that is column reference that where the "country" name at. Try changin
the column number and you will be amaze by the Vlookup formula.

stephen Le
 
R

Ron de Bruin

The OP send me a workbook private
He can use this macro in his situation


Sub Copy_With_AdvancedFilter()
Dim ws1 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long

Set ws1 = Sheets("DATA")
Set rng = ws1.Range("I1:W15")
'Use a Dynamic range name, http://www.contextures.com/xlNames01.html#Dynamic
'This example filter on the first column in the range (change this if needed)

With ws1
rng.Columns(8).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True
'You see that the last two columns of the worksheet are used to make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use this columns)
Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
.Range("IU1").Value = .Range("IV1").Value

For Each cell In .Range("IV2:IV" & Lrow)
.Range("IU2").Value = cell.Value

Set WSNew = Sheets.Add
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=WSNew.Range("A1"), _
Unique:=False
Next
.Columns("IU:IV").Clear
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


Ron de Bruin said:
Hi GazMo

You can find a macro example here
http://www.rondebruin.nl/copy5.htm
Click to expand...
 
Top