Split cells VBA
 

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

Split cells VBA
 

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

Split cells VBA
 

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

Split cells VBA
 

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

Split cells VBA
 

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

Split cells VBA
 

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


.

Split cells VBA
 

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

.