From: Diddy on
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
From: Bob Phillips on
What is the problem, the plethora of workbooks, the desire to process
multiple sheets, or what?

--

HTH

Bob

"Diddy" <Diddy(a)discussions.microsoft.com> wrote in message
news:A1DB568C-2725-43CD-9472-F31D3EF7843A(a)microsoft.com...
> 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


From: Mike H on
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
From: Per Jessen on
Hi Diddy,

Try one of theese:

With Worksheets(1)

or

With ActiveSheet

Regards,
Per


"Diddy" <Diddy(a)discussions.microsoft.com> skrev i meddelelsen
news:A1DB568C-2725-43CD-9472-F31D3EF7843A(a)microsoft.com...
> 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

From: Diddy on
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