Here's my problem:

I use a macro to reformat "headers" on a file that has hundreds of
"tables" aligned vertically in 1 worksheet. The original file is comma
delimited text so when I pull it into Excel, the words in my "headers"
become fragmented. My method of fixing this has been to fix the first
header, copy it (range of say a1:h3) and then use my macro below to
paste it at every occurance of "Table" - I first save off column A and
then repaste that after I run my macro to resore all the original table
numbers.

very brief example looks like this:
Table 1 - Misc
(data headers starting in col. C)
data...
Table 2 - More misc
(data headers starting in col. C)

data...
Table 3 - Even more misc
(data headers starting in col. C)

data...
===============

The problem occurs when I have the word "table" trigger a paste in a
place other than where it should (as in another column other than A or
another row within the table that happens to have the word in its
stub).

I hope this is understandable. I'm not sure how esle to explain it.

My macro is below. Can anyone offer suggestions on how I can further
qualify this "paste" to only the "Table"s I need?

Thank you!
Lisa



Here's my macro:
====================

Sub pasteit()
'
' pasteit Macro
' Macro recorded 6/23/2002 by Lisa
' Macro revised 8/11/2004 by Lisa
'
' Keyboard Shortcut: Ctrl+d
'
Cells.Find(What:="Table", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
True, SearchFormat:=False).Activate
ActiveSheet.Paste

Do Until ActiveCell.Address = "$A$1"
Cells.FindNext(After:=ActiveCell).Activate
ActiveSheet.Paste
ActiveSheet.Paste
Loop

MsgBox "The Header Macro is done!", , "FYI 1 of 2"
MsgBox "Don't forget to restore Column A to original!", , "FYI 2
of 2"
End



End Sub
======================


--
macromaiden
------------------------------------------------------------------------
macromaiden's Profile: http://www.excelforum.com/member.php...o&userid=31981
View this thread: http://www.excelforum.com/showthread...hreadid=517098

Restrict your search to column A:

Also revised your code to eliminate selecting and activating.

Sub pasteit()
'
' pasteit Macro
' Macro recorded 6/23/2002 by Lisa
' Macro revised 8/11/2004 by Lisa
'
' Keyboard Shortcut: Ctrl+d
'
Dim rng as Range, sAddr as String
set rng = Columns(1).Find(What:="Table", _
After:=Range("A65536"), LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext,MatchCase:=True, _
SearchFormat:=False)
if not rng is nothing then
sAddr = rng.Address

Do
rng.paste
set rng = Columns(1).FindNext(After:=rng)
Loop while rng.Address < sAddr
End if
MsgBox "The Header Macro is done!", , "FYI 1 of 2"
MsgBox "Don't forget to restore Column A to original!", _
, "FYI 2 of 2"
End
End Sub

--
Regards,
Tom Ogilvy

"macromaiden"
wrote in message
...

Here's my problem:

I use a macro to reformat "headers" on a file that has hundreds of
"tables" aligned vertically in 1 worksheet. The original file is comma
delimited text so when I pull it into Excel, the words in my "headers"
become fragmented. My method of fixing this has been to fix the first
header, copy it (range of say a1:h3) and then use my macro below to
paste it at every occurance of "Table" - I first save off column A and
then repaste that after I run my macro to resore all the original table
numbers.

very brief example looks like this:
Table 1 - Misc
(data headers starting in col. C)
data...
Table 2 - More misc
(data headers starting in col. C)

data...
Table 3 - Even more misc
(data headers starting in col. C)

data...
===============

The problem occurs when I have the word "table" trigger a paste in a
place other than where it should (as in another column other than A or
another row within the table that happens to have the word in its
stub).

I hope this is understandable. I'm not sure how esle to explain it.

My macro is below. Can anyone offer suggestions on how I can further
qualify this "paste" to only the "Table"s I need?

Thank you!
Lisa



Here's my macro:
====================

Sub pasteit()
'
' pasteit Macro
' Macro recorded 6/23/2002 by Lisa
' Macro revised 8/11/2004 by Lisa
'
' Keyboard Shortcut: Ctrl+d
'
Cells.Find(What:="Table", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
True, SearchFormat:=False).Activate
ActiveSheet.Paste

Do Until ActiveCell.Address = "$A$1"
Cells.FindNext(After:=ActiveCell).Activate
ActiveSheet.Paste
ActiveSheet.Paste
Loop

MsgBox "The Header Macro is done!", , "FYI 1 of 2"
MsgBox "Don't forget to restore Column A to original!", , "FYI 2
of 2"
End



End Sub
======================


--
macromaiden
------------------------------------------------------------------------
macromaiden's Profile:
http://www.excelforum.com/member.php...o&userid=31981
View this thread: http://www.excelforum.com/showthread...hreadid=517098

thank you Tom I'll give it a try...


--
macromaiden
------------------------------------------------------------------------
macromaiden's Profile: http://www.excelforum.com/member.php...o&userid=31981
View this thread: http://www.excelforum.com/showthread...hreadid=517098