Breaking up a sheet

Hi, I'm pretty new at this visual basic. I know what I want to do...don't know how to do it

I've got a list hundreds/thousands rows long, broken up in to section of 20-100 rows, and 10 or so columns wide.
What I want to do is take each section of the list and put it into its own sheet.

The first and last cell of the first column are the same for each section so this shouldn't be to hard.

I want to find the first value then the last value and select everything between.
Create a sheet and past into it.
Continue down the original sheet till there is nothing left.

I wrote this...but I'm getting some errors...Again, I'm pretty new, and if you could send some advice my way that would be great.

Thanks

Please Login or Register  to view this content.

Hi thisandthat

It would be helpful if you'd post a sample of your actual data showing what you have and what you'd like it to be (a before and after). Scrub the file of confidential information.

Some comments on your current coding:
------------------------------------------------------------------------
Option Explicit
Sub RunScript()
Application.ScreenUpdating = False
'1.
'Dim myRange, first, last, wksht, tempRange As String

'here, you tried to implicitly declare the list of variables as type STRING
'However, in VBA, you must explicitly declare the type of each variable:
Dim myRange As String, first As String, last As String, wksht As String, tempRange As String

'2.
Columns("A:A").Select
myRange = Selection.Address
' it is not necessary to select the column

'assign the variable directly:
myRange = "A:A"

wksht = "Data"

'3.
' you did not declare the variable "c" in the Dim statement above
' 'c' should be declared as type RANGE. Note that single letter variables
' make code hard to read; you shoud use a descriptive name as the others, say, TestCell.


Dim TestCell As Range

'4.
' the .Cells property is not needed here. So,
'For Each c In Worksheets(wksht).Range(myRange).Cells

'becomes
For Each TestCell In Worksheets(wksht).range(myRange)



'5.
'Here you have tested the contents of the cell in column A for a string match
'Then you load the var first with the contents of the cell if there is a match
'After testing the cell for tec name, you immediately test the same cell
' for another value which, it seems, should be found in a cell below the first
' so that you get a block of cells. So you need an ELSE statement to
' skip the second test if the first one is successful

If c.Value Like "*TEC Name:*" Then
first = c.Value
ElseIf c.Value Like "*99*" Then
last = c.Value
End If

'should look something like:
If TestCell.Value Like "*TEC Name:*" Then
first = TestCell.Address(0, 0)
ElseIf TestCell.Value Like "*99*" Then
last = TestCell.Address(0, 0)
End If

'6.
' now you add a new sheet trying to give it the name held in the cell in
' the next column. Unfortunately, you have put quotation marks around
' the code that was meant to point to the cell. The effect is that
' the actual sheet name will be the actual string: Range(first).Offset(0,1).Value!
' and not the contents of the cell
' previously, you loaded the var first with the string contents of the adjacent cell
' now you are trying to use the contents of first as cell address. Actually,
'first will contain something like "TEC Name". Thus
' Range(TEC Name:xxxx) is in error. It seems like you wanted the address of the current TestCell:
' I.E.,
first = TestCell.Address(0, 0)

'and
'Worksheets.Add().Name = "Range(first).Offset(0,1).Value"
'becomes
Worksheets.Add().Name = Range(first).Offset(0, 1).Value
Sheets(wksht).Select

'7.
' in the code below, you are using the CELLS property.
' The Cells, requires two INDEX NUMBERs. If you are using
' variables to hold the numbers they must be Declared as type Long
' but you have declared "first" & "last" as strings, not numbers
' additionally, above you have loaded first with the string contents of a cell

'8.
' for the code below would look like:
' Cell(Row_var, X); where your Row_var is declared as Long.

Dim StartRow As Long, StopRow As Long

' and
Range(Cells(first, 1), Cells(last, 7)).Copy
'becomes
Range(Cells(StartRow, 1), Cells(StopRow, 7)).Copy


Sheets(Range(first).Offset(0, 1).Value).Select

'9.
' Here, you are missing quotes around the "ADDRESS STRING": "B2". Because there are no enclosing quotes, B2 is treated as a variable which is empty, in this case.
Range(B2).PasteSpecial

'10.
Notice that this End If is a part of the If c.Value Like "*99*" Then.. block. The effect of having that END IF in this position would cause the test for *TEC Name:* to do nothing even if true since all the remaining code is within the second IF/Then block.

Next c
Application.ScreenUpdating = True
End Sub

The code below isn't tested since there was no attached Workbook"

Please Login or Register  to view this content.