Loop in copy paste code

[Volnixx]

Hello

I wrote a code that copies the cells from a specific range in Excel and pastes them to a specific Bookmark in Word.

The code looks something like this:

Application.Goto ActiveWorkbook.Sheets("Sheet1").Range("A1:W15")
Selection copy
WordDoc.Bookmarks("Bookmark1").Select
WordApp.Selection.Paste
Application.Goto ActiveWorkbook.Sheets("Sheet2").Range("A1:W15")
Selection copy
WordDoc.Bookmarks("Bookmark2").Select
WordApp.Selection.Paste

The problem now is that allthought I wrote the code allready the code now has ended being 120 pages or something and it is very Mouse Wheel to browse through

Do you think there is a way to loop the process? Can I use variables or something else where the ranges in excel and where the bookmarks are written in the code?




Thank you very much

[Siddharth Rout]

Logically, Yes, you can do it. Programatically, it depends on how are your ranges placed. What are the names of the bookmark. Does the name of the bookmark follow a pattern etc... etc...

Few Tips

Sheets("Sheet1") can also be written as Sheets(1)
Range("A1") can also be written as Range("A" & i)
same goes with bookmark...

See the following code. The code doesn't do anything... It is just to show you how to work with your code...

Sub Example()
    i = 2
    j = 5
    a = "Sheet" & i '<-- "Sheet2"
    Sheets(a).Range("A" & i).Copy '<--- Range("A2")
    Bmark = "Bookmark" & i '<--- "Bookmark2"
    Rng = Range("A" & i & ":B" & j).Address '<--- Range("A2:B5")
End Sub

Try it out. If you still get stuck... Yell This forum will definitely help you out. It always does