Moving data between worksheets in VBA
 

Hi all,

I'm trying to evaluate a list in a column. When I reach a row that contains
a "=", I'd like to create a new worksheet and cut and paste all the entries
following into that new sheet.

For example:

Sheet 1:
Monkeys
Cinnamon
Car Keys
=
Root Beer

Sheet 2:
Root Beer

Any thoughts on the best way to approach the code?

Thanks in advance, this forum is amazing!

Mike

Moving data between worksheets in VBA
 

Hi Mike,
Try:
'-------------------------------------
Sub test()
Dim WshS As Worksheet, WshD As Worksheet 'Source, Destination sheets
Dim Rg, RgS As Range, RgD As Range 'a range, source, dest. ranges

Set WshS = Worksheets("sheet1")
Set RgS = WshS.Range("A:A")

Set Rg = RgS.Find(what:="=", after:=RgS.Cells(RgS.Cells.Count),
LookIn:=xlValues, lookat:=xlWhole)
If Rg Is Nothing Then 'doesn't exist
MsgBox "Cannot find =."
Else
'add a sheet
Set WshD = Worksheets.Add
Set Rg = Range(Rg.Offset(1, 0), Rg.Parent.Cells(65536,
Rg.Column).End(xlUp))
Application.CutCopyMode = False
Rg.Copy WshD.Range("A1")
End If
End Sub
'-------------------------------------------

Regards,
Sebastien


"Mike" wrote:

Hi all,

I'm trying to evaluate a list in a column. When I reach a row that contains
a "=", I'd like to create a new worksheet and cut and paste all the entries
following into that new sheet.

For example:

Sheet 1:
Monkeys
Cinnamon
Car Keys
=
Root Beer

Sheet 2:
Root Beer

Any thoughts on the best way to approach the code?

Thanks in advance, this forum is amazing!

Mike

Moving data between worksheets in VBA
 

Here is some code...
A little shorter than Sebastiens but not a remarkably different...

Private Const SEARCHSTRING As String = "="

Public Sub TransferOnEqual()
Dim rngStart As Range
Dim rngFound As Range
Dim rngToSearch As Range

Set rngToSearch = Sheet1.Range("A1").EntireColumn
Set rngFound = rngToSearch.Find(SEARCHSTRING, , , xlWhole)
Set rngStart = rngFound

If rngFound Is Nothing Then
MsgBox "Nothin Found"
Else
Do
rngFound.Offset(1, 0).EntireRow.Copy
Sheets.Add
ActiveSheet.Paste
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngStart.Address = rngFound.Address
End If

End Sub

"Mike" wrote:

Hi all,

I'm trying to evaluate a list in a column. When I reach a row that contains
a "=", I'd like to create a new worksheet and cut and paste all the entries
following into that new sheet.

For example:

Sheet 1:
Monkeys
Cinnamon
Car Keys
=
Root Beer

Sheet 2:
Root Beer

Any thoughts on the best way to approach the code?

Thanks in advance, this forum is amazing!

Mike