Hi everyone,

I've been using the following (kindly supplied by Rick Rothstein) to split
address items.

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Sections() As String
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Sections = Split(.Cells(X, "A").Value, "/")
For Z = 0 To UBound(Sections)
..Cells(X, Z + 2).Value = Sections(Z)
Next
Next
End With
End Sub

I now have lots of single sheet wkbks which have named sheets. I've tried
taking the " " out from the With Worksheets("Sheet1") and also calling it
Activesheet getting very mixed up :-(

If anyone can help (possibly explain what's foxing me) that would be great!

Thanks
Diddy

What is the problem, the plethora of workbooks, the desire to process
multiple sheets, or what?

--

HTH

Bob

"Diddy" wrote in message
...
Hi everyone,

I've been using the following (kindly supplied by Rick Rothstein) to split
address items.

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Sections() As String
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Sections = Split(.Cells(X, "A").Value, "/")
For Z = 0 To UBound(Sections)
.Cells(X, Z + 2).Value = Sections(Z)
Next
Next
End With
End Sub

I now have lots of single sheet wkbks which have named sheets. I've tried
taking the " " out from the With Worksheets("Sheet1") and also calling it
Activesheet getting very mixed up :-(

If anyone can help (possibly explain what's foxing me) that would be
great!

Thanks
Diddy

Hi Bob,

Being truthful, the basic problem is lack of understanding of objects etc so
when I had anything out of the ordinary (i.e. the different wkbks had sheets
which had names specific to the wkbk) I didn't know what to do :-(

Cheers
Diddy (learning very slowly!)



"Bob Phillips" wrote:

What is the problem, the plethora of workbooks, the desire to process
multiple sheets, or what?

--

HTH

Bob

"Diddy" wrote in message
...
Hi everyone,

I've been using the following (kindly supplied by Rick Rothstein) to split
address items.

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Sections() As String
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Sections = Split(.Cells(X, "A").Value, "/")
For Z = 0 To UBound(Sections)
.Cells(X, Z + 2).Value = Sections(Z)
Next
Next
End With
End Sub

I now have lots of single sheet wkbks which have named sheets. I've tried
taking the " " out from the With Worksheets("Sheet1") and also calling it
Activesheet getting very mixed up :-(

If anyone can help (possibly explain what's foxing me) that would be
great!

Thanks
Diddy


.

Hi,

Activesheet shout work in a single sheet workbook so you could try

With ActiveSheet

This should work also

With Sheet1.

Note we've now dropped the quotes because were not using a sheet name
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Diddy" wrote:

Hi everyone,

I've been using the following (kindly supplied by Rick Rothstein) to split
address items.

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Sections() As String
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Sections = Split(.Cells(X, "A").Value, "/")
For Z = 0 To UBound(Sections)
.Cells(X, Z + 2).Value = Sections(Z)
Next
Next
End With
End Sub

I now have lots of single sheet wkbks which have named sheets. I've tried
taking the " " out from the With Worksheets("Sheet1") and also calling it
Activesheet getting very mixed up :-(

If anyone can help (possibly explain what's foxing me) that would be great!

Thanks
Diddy

Hi Mike,

Both worked :-)

Cheers
Diddy

"Mike H" wrote:

Hi,

Activesheet shout work in a single sheet workbook so you could try

With ActiveSheet

This should work also

With Sheet1.

Note we've now dropped the quotes because were not using a sheet name
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Diddy" wrote:

Hi everyone,

I've been using the following (kindly supplied by Rick Rothstein) to split
address items.

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Sections() As String
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Sections = Split(.Cells(X, "A").Value, "/")
For Z = 0 To UBound(Sections)
.Cells(X, Z + 2).Value = Sections(Z)
Next
Next
End With
End Sub

I now have lots of single sheet wkbks which have named sheets. I've tried
taking the " " out from the With Worksheets("Sheet1") and also calling it
Activesheet getting very mixed up :-(

If anyone can help (possibly explain what's foxing me) that would be great!

Thanks
Diddy

Hi Diddy,

Try one of theese:

With Worksheets(1)

or

With ActiveSheet

Regards,
Per


"Diddy" skrev i meddelelsen
...
Hi everyone,

I've been using the following (kindly supplied by Rick Rothstein) to split
address items.

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Sections() As String
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Sections = Split(.Cells(X, "A").Value, "/")
For Z = 0 To UBound(Sections)
.Cells(X, Z + 2).Value = Sections(Z)
Next
Next
End With
End Sub

I now have lots of single sheet wkbks which have named sheets. I've tried
taking the " " out from the With Worksheets("Sheet1") and also calling it
Activesheet getting very mixed up :-(

If anyone can help (possibly explain what's foxing me) that would be
great!

Thanks
Diddy

Thank you Per,

Cheers
Diddy


"Per Jessen" wrote:

Hi Diddy,

Try one of theese:

With Worksheets(1)

or

With ActiveSheet

Regards,
Per


"Diddy" skrev i meddelelsen
...
Hi everyone,

I've been using the following (kindly supplied by Rick Rothstein) to split
address items.

Sub SplitCells()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Sections() As String
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Sections = Split(.Cells(X, "A").Value, "/")
For Z = 0 To UBound(Sections)
.Cells(X, Z + 2).Value = Sections(Z)
Next
Next
End With
End Sub

I now have lots of single sheet wkbks which have named sheets. I've tried
taking the " " out from the With Worksheets("Sheet1") and also calling it
Activesheet getting very mixed up :-(

If anyone can help (possibly explain what's foxing me) that would be
great!

Thanks
Diddy

.