Need Help in Copying a row from a spreadsheet to another by its value

[Benastros]

Hello,

I am working on a Excel document, and I am trying to copy data over that have a value to sheet.

For Example,

I have 7 colums starting from B3 to H3, and what I am trying to do is copy the rows based on location to that sheet. Each sheet will be named by location so for example, Houston will have a sheet named Houston, and I want to copy all the rows to the Houston sheet, and so forth with the other locations. I know I can manually copy and paste them over, but is there away to do this just by entering the location on the main sheet page, and have it go to the desired location. I Hope this makes sense.

Thanks,

Ben

Service Tag	Name	Date Received	Assigned to	Location	Model	Ticket
12222	wsdt012	8/12/12	Bob Smith	Houston	E-6400	7447
44444	wsdt111	8/12/12	Adam Jones	Houston	E-6410	7477
77777	wsnb112	8/12/12	Ben Cade	Chicago	E-6420	7452
77778	wsdt065	8/12/12	Lisa Wise	New York	E-6400	5255

<tbody>
</tbody>

 

[dmt32]

Hello,

I am working on a Excel document, and I am trying to copy data over that have a value to sheet.

For Example,

I have 7 colums starting from B3 to H3, and what I am trying to do is copy the rows based on location to that sheet. Each sheet will be named by location so for example, Houston will have a sheet named Houston, and I want to copy all the rows to the Houston sheet, and so forth with the other locations. I know I can manually copy and paste them over, but is there away to do this just by entering the location on the main sheet page, and have it go to the desired location. I Hope this makes sense.

Thanks,

Ben

Service Tag	Name	Date Received	Assigned to	Location	Model	Ticket
12222	wsdt012	8/12/12	Bob Smith	Houston	E-6400	7447
44444	wsdt111	8/12/12	Adam Jones	Houston	E-6410	7477
77777	wsnb112	8/12/12	Ben Cade	Chicago	E-6420	7452
77778	wsdt065	8/12/12	Lisa Wise	New York	E-6400	5255

<TBODY>
</TBODY>

not tested but this may do what you want. Place both procedures in standard module & adjust as required.

dave
Option Explicit
Sub Locations()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim Datarng As Range
Dim rowcount As Long
Dim location As Range
Set ws1 = Sheets("Sheet1") '<< your master sheet rename as required
Application.ScreenUpdating = False
With ws1
.Activate
.Unprotect Password:="" 'add password if needed
rowcount = .Cells(.Rows.Count, "B").End(xlUp).Row
Set Datarng = .Range("B3:H" & rowcount)
.Range("F3:F" & rowcount).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("J1"), _
Unique:=True
rowcount = .Cells(.Rows.Count, "J").End(xlUp).Row
'set Criteria
.Range("L1").Value = .Range("F3").Value

For Each location In .Range("J2:J" & rowcount)
'add the location to criteria
.Range("L2").Value = location.Value
'if location sheet exists
'update it
If SheetExists(location.Value) Then

Sheets(location.Value).Cells.Clear
Datarng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ws1.Range("L1:L2"), _
CopyToRange:=Sheets(location.Value).Range("A1"), _
Unique:=False
Else
'add new sheet
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = location.Value
Datarng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ws1.Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If
Next

.Select

.Columns("J:L").Clear

End With
Application.ScreenUpdating = True
End Sub

Function SheetExists(wksName As String) As Boolean
On Error Resume Next
SheetExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

 

[Benastros]

Sweet!!, this works like a charm!!! You made my week


Thanks,

Ben

 

[dmt32]

Sweet!!, this works like a charm!!! You made my week


Thanks,

Ben

Hi Ben,
glad I could help

Dave