Macro Code to split data in the same sheet

[Reese03]

Hi,

Need help. I have my excel assignment, were i have this table needed to be separated using macro.

Bartlett, Tara</SPAN>

Cain, Lindsey</SPAN>

Carmack, William</SPAN>

Martin, Tammie</SPAN>

Ordez, Marcelena</SPAN>

Rose, Chad</SPAN>

Swanson, Michael</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

I started to try it by using Text to Columns, but I just have to separate this data and add headers to each columns. I just have to show in macro that I have separated this and change the format of the data.

Help.


Thanks.

 

[My Aswer Is This]

Some members of Mr. Excel say we do not answer student's assignments. It may help you more by you trying to work this out instead of us giving you the answer.

 

[hiker95]

Reese03,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


This site is a perfect place to further ones education in Excel, NOT, repeat NOT a place to provide FREE Applications. Show us your desire to "learn", not just "Can someone do my homework for me?"

We are not going to do your homework for you. But, we can discuss how you would proceed to solve the homework assignment.


Can you give us some background of the class work leading up to the assignment (Excel commands, functions, formulae)?

I just have to separate this data and add headers to each columns

3. What cell contains Bartlett, Tara?

4. What are the new titles/header names?

5. What cells are the new titles/header names in?

6. What information from the names go under each title/header names?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[iliace]

I would suggest to record your actions as a macro, then use that as the basis for your code. Excel's Macro Recorder generates pretty bad code, but it will give you a good idea of what properties and methods to use.

 

[Reese03]

Hi,

Here's the instructions given for this assignment:

Using a button, write a marco for separating the last name, and first name into separate columns and add in the appropriate headers for each.

Bartlett, Tara</SPAN>

Cain, Lindsey</SPAN>

Carmack, William</SPAN>

Martin, Tammie</SPAN>

Ordez, Marcelena</SPAN>

Rose, Chad</SPAN>

Swanson, Michael</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

I already tried to record actions using macro, put on the headers like last name and first name, but when I tried to run the Macro, what I'm getting is the old format of the given sample. No changes at all. Im using PC, and Windows and Excel 2007.

 

[hiker95]

Reese03,

You have not answered all my questions?????


Can we see your macro code?


When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[Reese03]

Hi,


1. What version of Excel and Windows are you using? - Excel / Windows 2007

2. Are you using a PC or a Mac? - Im using PC.


We are not going to do your homework for you. But, we can discuss how you would proceed to solve the homework assignment.

3. What cell contains Bartlett, Tara?
4. What are the new titles/header names? - Last Name and First Name (headers) color filled in Dark Blue

5. What cells are the new titles/header names in? -

6. What information from the names go under each title/header names?

Here's the VBA code I created:

Sub Macro2Macro1Updated()
'
' Macro2Macro1Updated Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://investing.money.msn.com/investments/market-index/?symbol=$GB:UKX", _
Destination:=Range("$C$7"))
.Name = "UKX_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("C7:H17").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("C7:H7").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
Range("C7:H7").Select
Selection.Copy
Range("C21").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").Select

MsgBox "Action Completed"

End Sub

 

[hiker95]

Reese03,

You still have not answered all my questions?????

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://investing.money.msn.com/investments/market-index/?symbol=$GB:UKX", _
Destination:=Range("$C$7"))

I have no experience with the above.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.