Split cells VBA

  • Thread starter Thread starter Diddy
  • Start date Start date
D

Diddy

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?
 
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.
 
Hi Mike,

Both worked :-)

Cheers
Diddy

Mike H said:
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.
Click to expand...
 
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!)
 
Back
Top