Hi,

This may be a pretty complicated coding situation but I was hoping
that somebody might be able to help out.

I am trying to copy rows based on a certain value found in Column B

For example, If the value 1 exists in B I would like to copy all of
the rows that contain that value in column B and paste it into a new
workbook.

If the value in column B equals 2 then I would like to copy all of
those values into a different workbook

My problem arises because sometimes column B may contain values up to
15 in one instance while in another it may contain values up to 18 so
I cannot hard code the macro.

So could anybody help me with some code to scan column B and copy rows
based on the values that exist.

Here is an example of the code I have found to copy 1 value to a
different workbook

this macro assumes that your first row of data is a header row.
'will copy a row from one worksheet, to another blank workbook
'IF there is a 0 in column N
'Variables used by the macro
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String

'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Select Range
'(note you can change this to meet your requirements)
Range("A1:AS3000").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = 1
'NOTE - this filter is on column N (field:=14), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=2, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
'Open a new file
Workbooks.Add Template:="Workbook"
'Get this file's name
NewFileName = ActiveWorkbook.Name
'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original file
Workbooks(CurrentFileName).Activate
'Clear the autofilter
Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Thanks so much for the help.

Sincerely,
Ben

Do you want to selectively copy values (you have a list to copy), or do you
just want to split all the different values in Column B to a new workbook for
each value?

Can the values in column B be sorted?

Barb Reinhardt




"Moneyball Wilson" wrote:

Hi,

This may be a pretty complicated coding situation but I was hoping
that somebody might be able to help out.

I am trying to copy rows based on a certain value found in Column B

For example, If the value 1 exists in B I would like to copy all of
the rows that contain that value in column B and paste it into a new
workbook.

If the value in column B equals 2 then I would like to copy all of
those values into a different workbook

My problem arises because sometimes column B may contain values up to
15 in one instance while in another it may contain values up to 18 so
I cannot hard code the macro.

So could anybody help me with some code to scan column B and copy rows
based on the values that exist.

Here is an example of the code I have found to copy 1 value to a
different workbook

this macro assumes that your first row of data is a header row.
'will copy a row from one worksheet, to another blank workbook
'IF there is a 0 in column N
'Variables used by the macro
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String

'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Select Range
'(note you can change this to meet your requirements)
Range("A1:AS3000").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = 1
'NOTE - this filter is on column N (field:=14), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=2, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
'Open a new file
Workbooks.Add Template:="Workbook"
'Get this file's name
NewFileName = ActiveWorkbook.Name
'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original file
Workbooks(CurrentFileName).Activate
'Clear the autofilter
Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Thanks so much for the help.

Sincerely,
Ben

Hi Moneyball

See
http://www.rondebruin.nl/copy5.htm#workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Moneyball Wilson" wrote in message
...
Hi,

This may be a pretty complicated coding situation but I was hoping
that somebody might be able to help out.

I am trying to copy rows based on a certain value found in Column B

For example, If the value 1 exists in B I would like to copy all of
the rows that contain that value in column B and paste it into a new
workbook.

If the value in column B equals 2 then I would like to copy all of
those values into a different workbook

My problem arises because sometimes column B may contain values up to
15 in one instance while in another it may contain values up to 18 so
I cannot hard code the macro.

So could anybody help me with some code to scan column B and copy rows
based on the values that exist.

Here is an example of the code I have found to copy 1 value to a
different workbook

this macro assumes that your first row of data is a header row.
'will copy a row from one worksheet, to another blank workbook
'IF there is a 0 in column N
'Variables used by the macro
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String

'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Select Range
'(note you can change this to meet your requirements)
Range("A1:AS3000").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = 1
'NOTE - this filter is on column N (field:=14), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=2, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
'Open a new file
Workbooks.Add Template:="Workbook"
'Get this file's name
NewFileName = ActiveWorkbook.Name
'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original file
Workbooks(CurrentFileName).Activate
'Clear the autofilter
Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Thanks so much for the help.

Sincerely,
Ben