macro help

[gray78]

I have a word document and an excel worksheet. I am trying to create macros within the document which will go to the worksheet, copy a range of cells and paste special (microsoft office excel worksheet object) the copied cells an then format the object to fit the document. I am trying to do this in several different sections of the document with different cell ranges of the worksheet. The problem I am now having is that when I close the document or worksheet, and the copied cache clears, I get an error the next time I try to run the macros. Any suggestions?

 

[fumei]

Ummmm, can you post some code?

Gerry

 

[TonyJollans]

I second that. You're not really making sense; can we have some more detail please? The only 'copied caches' are the Windows clipboard and the Office Clipboard (unless you have some other clipboard software installed). By definition they are temporary and no solution based on them is going to work.

That said, if you are, as you say, copying and pasting each time then caching is an irrelevance.

Lastly, if you are pasting-special the Worksheet object that does actually insert the whole workbook in your document and doing it multiple times in the same document probably isn't wise.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[gray78]

More detail:
I have a monthly report being created in a MS Word document. Within this document I have several tables which are from an MS Excel worksheet. These are the tables or actually a range of cells which I am coping and pasting (through macros) into the word document.

Is there a way to link these ranges of cells from Excel into Word?

I hope I have explained this a bit better.

Gray 78

 

[fumei]

More detail:
I have a monthly report being created in a MS Word document.

That is nice...but absolutely irrelevant. Would it make a difference if it was a weekly report?

Within this document I have several tables which are from an MS Excel worksheet. These are the tables or actually a range of cells which I am coping and pasting (through macros) into the word document.

Through macros...again, that is nice...but as we can't see what you ARE doing, it is hard to suggest what you CAN do.

Is there a way to link these ranges of cells from Excel into Word?

Yes...there is.

I hope I have explained this a bit better.

No you have not. Again, perhaps if you posted some code?

Gerry

 

[gray78]

code:
Sub ot_18()
'
' ot_18 Macro
' Macro recorded 4/7/2006 by dkotcher
'
Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement _
:=wdInLine, DisplayAsIcon:=False
Selection.TypeBackspace
Selection.InlineShapes(1).Fill.Visible = msoFalse
Selection.InlineShapes(1).Fill.Solid
Selection.InlineShapes(1).Fill.Transparency = 0#
Selection.InlineShapes(1).Line.Weight = 0.75
Selection.InlineShapes(1).Line.Transparency = 0#
Selection.InlineShapes(1).Line.Visible = msoFalse
Selection.InlineShapes(1).LockAspectRatio = msoTrue
Selection.InlineShapes(1).Height = 337.7
Selection.InlineShapes(1).Width = 558#
Selection.InlineShapes(1).PictureFormat.Brightness = 0.5
Selection.InlineShapes(1).PictureFormat.Contrast = 0.5
Selection.InlineShapes(1).PictureFormat.ColorType = msoPictureAutomatic
Selection.InlineShapes(1).PictureFormat.CropLeft = 0#
Selection.InlineShapes(1).PictureFormat.CropRight = 0#
Selection.InlineShapes(1).PictureFormat.CropTop = 0#
Selection.InlineShapes(1).PictureFormat.CropBottom = 0#
End Sub

 

[AnotherHiggins]

For future reference, please post Macro questions in forum707, the VBA Visual Basic for Applications (Microsoft) Forum.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[fumei]

Agreed.

gray78, could you please repost in the VBA forum. And post all your relevant code. What you posted is insufficient.

Gerry