Moce rows with N in Col A to a new sheet
 

Hi. I have a database of info with thousands of lines in Sheet1. In column
A I have an Include indicator (Y/N). I would like to take all rows with N
in column A and move them to Sheet2. Possible?

Moce rows with N in Col A to a new sheet
 

Hi Steph,

One way would be to use Excel's Advanced Filter, setting a filter criterion
for your inclusion indicator, selecting the Copy to another location option.
Run the filter from the destination sheet.

If you want to do this in VBA, you could turn on the macro recorder and
review the resultant code. If you have problems with this, post back and
someone will assist.


---
Regards,
Norman



"Steph" wrote in message
...
Hi. I have a database of info with thousands of lines in Sheet1. In
column A I have an Include indicator (Y/N). I would like to take all rows
with N in column A and move them to Sheet2. Possible?

Moce rows with N in Col A to a new sheet
 

Use an autofilter (Data=Filter=Autofilter) Select column 1 dropdown and
select N

then select all the data and copy it to sheet 2.

If you want a macro, turn on the macro recorder while you do it manually.

An alternative would be to use an Advanced filter

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi. I have a database of info with thousands of lines in Sheet1. In
column
A I have an Include indicator (Y/N). I would like to take all rows with N
in column A and move them to Sheet2. Possible?

Moce rows with N in Col A to a new sheet
 

"Steph" wrote ...

Hi. I have a database of info with thousands of lines in Sheet1. In column
A I have an Include indicator (Y/N). I would like to take all rows with N
in column A and move them to Sheet2. Possible?

This is .Excel.Programming, so how about some VBA? This following
assumes the destination sheet 'Sheet2' does not already exist:

Option Explicit

Sub test()
CopyToNewWorksheet "Sheet1", "Sheet2"
End Sub

Private Function CopyToNewWorksheet( _
ByVal SheetName As String, _
Optional ByVal NewSheetName As String _
) As Boolean

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Target As Excel.Range
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String
Dim lngCounter As Long

' Review the following constant:
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"

Const TABLE_XL_TEMP As String = "" & _
"test_only"

' Do NOT amend the following constants
Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

Const SQL As String = "" & _
"SELECT * FROM [<SHEET_NAME$]" & _
" WHERE Include = 'N';"

' Build connection string
strPath = ThisWorkbook.Path & _
Application.PathSeparator

strCon = CONN_STRING
strCon = Replace(strCon, _
"<PATH", strPath)
strCon = Replace(strCon, _
"<FILENAME", FILENAME_XL_TEMP)

' Build sql statement
strSql1 = SQL
strSql1 = Replace(strSql1, _
"<SHEET_NAME", TABLE_XL_TEMP)

' Delete old instance of temp workbook
On Error Resume Next
Kill strPath & FILENAME_XL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(SheetName). _
Copy .Worksheets(1)
.Worksheets(1).Name = TABLE_XL_TEMP
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.Open

Set rs = .Execute(strSql1)
End With

Set ws = ThisWorkbook.Worksheets.Add
With ws
If Len(NewSheetName) 0 Then
.Name = NewSheetName
End If
Set Target = .Range("A1")
End With

With rs
For lngCounter = 1 To .fields.Count
Target(1, lngCounter).Value = _
.fields(lngCounter - 1).Name
Next
End With

Target(2, 1).CopyFromRecordset rs

Con.Close

CopyToNewWorksheet = True

End Function


Jamie.

--