PasteSpecial+Bold+Center+Font Size

[demolay]

Hello,

Initially, I was having hard time with finding a macro code to do paste special from excel to ppt. Simply, I copy some cells in excel, go to ppt and select some cells/rows in a table, paste special as unformatted text, then make them bold, centered and font size=12.

I found this code to be useful in making paste special part:
PowerPoint.Application.ActiveWindow.View.pastespecial (ppPasteText)

But I cannot make my text bold, centered and font size. My text is in a table. I dont want to make the whole table bold. I just want to make copy-pasted part bold. And there is no fixed part of table that I do paste. Sometimes it is 2nd row, sometimes 1st column, etc.

I dont know almost anything about VBA, I just try to create macros by manipulating existing knowledge on web. It works great for excel, but ppt macros are really hard for me. So, if you dont get my question, I can further inform you.

Thanks in advance.

[Zack Barresse]

Hi there, welcome to the board!

You could use something like this to format your table...

[vba]Sub FormatSelectedTable()
Dim iRow As Long, iCol As Long
On Error GoTo ErrorHandle
With ActiveWindow.Selection.ShapeRange(1)
If .Type <> msoTable Then GoTo ErrorHandle
.Table.Cell(2, 3).Shape.TextFrame.TextRange.Font.Bold = msoTrue
.Table.Cell(2, 3).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
.Table.Cell(2, 3).Shape.TextFrame.TextRange.Font.Size = 12
End With
Exit Sub
ErrorHandle:
MsgBox "You do not have a table selected."
End Sub[/vba]

Other than that I'm not sure how to figure out what the part of the table is you pasted. The active cell perhaps?

HTH

[demolay]

Yes, I need to paste to active cells.

Simply I do the following manually: Copy some cells from excel, go to ppt, select some cells in a table, then paste special as unformatted text, do bold, do font 12, do align center.

I need to have macro that do the part starting from paste special. So, I will copy some cells from excel, go to ppt, select some cells in a table, and click macro.

[Zack Barresse]

No, but if it's selected, you should be able to do that...

[vba]Sub FormatSelectedTable()
Dim iRow As Long, iCol As Long, iC As Long, iR As Long
On Error GoTo ErrorHandle
With ActiveWindow.Selection.ShapeRange(1)
If .Type <> msoTable Then GoTo ErrorHandle
For iR = 1 To .Table.Rows.Count
For iC = 1 To .Table.Columns.Count
If .Table.Cell(iR, iC).Selected = True Then
.Table.Cell(iR, iC).Shape.TextFrame.TextRange.Font.Bold = msoTrue
.Table.Cell(iR, iC).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
.Table.Cell(iR, iC).Shape.TextFrame.TextRange.Font.Size = 12
Exit For
End If
Next iC
Next iR
End With
Exit Sub
ErrorHandle:
MsgBox "You do not have a table selected."
End Sub[/vba]

Haven't tested this new code, unable to at the moment.

[demolay]

I select cells, run macro, but nothing happens
No error, no change in text...

[John Wilson]

I select cells, run macro, but nothing happens
No error, no change in text...

Maybe this ammendment?

[vba]Sub FormatSelectedTable()
Dim iC As Integer, iR As Integer
On Error GoTo ErrorHandle
With ActiveWindow.Selection.ShapeRange(1)
If .Type <> msoTable Then GoTo ErrorHandle
For iR = 1 To .Table.Rows.Count
For iC = 1 To .Table.Columns.Count
If .Table.Cell(iR, iC).Selected Then
.Table.Cell(iR, iC).Shape.TextFrame.TextRange.Font.Bold = msoTrue
.Table.Cell(iR, iC).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
.Table.Cell(iR, iC).Shape.TextFrame.TextRange.Font.Size = 12
End If
Next iC
Next iR
End With
Exit Sub
ErrorHandle:
MsgBox "You do not have a table selected."
End Sub[/vba]

[demolay]

Wow, that works!
You cannot imagine how much time this macro going to save.
Thanks a lot.

[John Wilson]

Glad it helped! Post money to Zack and he can buy me an Alaskan Ale at the MVP Summit in March! ;o)