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" <(E-Mail Removed)> wrote in message
news:8ef27b0e-3395-4f54-80fa-(E-Mail Removed)...
> 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