Loop & copy/paste particular number of rows

[kklaus]

I'm hoping you guys can help me with a simple loop problem. I have some VB code that takes a particular range of cells and copies and pastes them into a Word doc as a picture. The users have the ability to show and hide rows as needed in the worksheet. I need to just pick up the first 45 visible rows and copy/paste those into my doc. Then pick up the next 45 visible rows and copy/paste those and so on until it gets to row 329, then it should stop.

Below is what I have without the looping. I'm a real newbie, so be gently with me.

Dim source As Range
Dim wdApp As Object
Dim wdDoc As Object

'Create a hidden instance of Word
Set wdApp = CreateObject("Word.Application")
'Add a document.
Set wdDoc = wdApp.Documents.Add
  
With ActiveSheet
Set source = .Range("A289:F329").SpecialCells(xlCellTypeVisible)
source.Copy
End With

'name the .doc file from a cell on the sheet
strname = ThisWorkbook.Sheets("Quote Letter").Range("E14").Value & " Quote Letter"

With wdDoc
    wdDoc.PageSetup.LeftMargin = "0.5"
    wdDoc.PageSetup.RightMargin = "0.5"
    wdDoc.PageSetup.TopMargin = "0.75"
    wdDoc.PageSetup.BottomMargin = "0.75"
    'Paste the source-range into the active document as a picture
    wdApp.Selection.Range.PasteSpecial Link:=False, DataType:=3, _
    Placement:=wdInLine, DisplayAsIcon:=False
   
    wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
    wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
End With


With ActiveSheet
Set source = .Range("A70:F287").SpecialCells(xlCellTypeVisible)
source.Copy
End With


With wdDoc
    wdDoc.PageSetup.LeftMargin = "0.5"
    wdDoc.PageSetup.RightMargin = "0.5"
    wdDoc.PageSetup.TopMargin = "0.75"
    wdDoc.PageSetup.BottomMargin = "0.75"
    wdApp.Selection.Range.PasteSpecial Link:=False, DataType:=3, _
    Placement:=wdInLine, DisplayAsIcon:=False
    wdApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
End With
    
With ActiveSheet
    Set source = .Range("A1:F69").SpecialCells(xlCellTypeVisible)
    source.Copy
End With


With wdDoc
    wdDoc.PageSetup.LeftMargin = "0.5"
    wdDoc.PageSetup.RightMargin = "0.5"
    wdDoc.PageSetup.TopMargin = "0.75"
    wdDoc.PageSetup.BottomMargin = "0.75"
    wdApp.Selection.Range.PasteSpecial Link:=False, DataType:=3, _
    Placement:=wdInLine, DisplayAsIcon:=False
End With
    
With wdDoc
'Save & Close the document
.SaveAs ThisWorkbook.Path & "\" & strname & ".doc"
.Close
End With

'Close the hidden instance of Word
wdApp.Quit

Application.CutCopyMode = False

 

[kklaus]

One more thing...I kept saying 45 "rows" but I really need Columns A:F for 45 rows.

Example:
A1:F45 (if they are all visible)
A46:F91 (if they are all visible)

etc

I hope I didn't further complicate what I was trying to get across.

 

[kklaus]

Any thoughts yet?

Thanks!

 

[Gavona]

I assume that your code works currently except that you have to manually split Source into ranges including no more than 45 rows?

If none of the experts reply then how about copying and pasting the visible rows/range to a new sheet.
Then it would be easier to use code along the lines of:

for i = 1 to source2.rows.count step 45
copyrange = range(cells(i,5),cells(i+45,5)

nb the above is to give you an idea it is not properly configured code (I don't have excel in front of me and am not an expert)

Thanks,

Gavin

 

[kklaus]

OK. That's definitely going in the right direction! I feel like it's really close. However, I am obviously doing something wrong in my loop.

1. When I open the .doc file it has my rows 1:45 pasted at the end of the document instead of the beginning. Then my second set of 45 rows pasted as the second to last image and so on. How do I get the first set of rows to be the 1st image in the document, 2nd set of rows to be the 2nd image, etc?

2. Also, it's copying and pasting the first 45 rows not the first 45 visible rows. I have about 15 various rows hidden, so I would like the code to copy only the visible rows which in this case is rows 1:60. The hidden rows may change from worksheet to worksheet, so I can't always say copy rows 1:60. It could be only 5 hidden rows next time and I may need to copy rows 1:50.

3. Lastly, I want the last row that is copied always to be row 327 but I'm not sure how to do that.

With ActiveSheet
For i = 1 To 282 Step 45
   Set source = .Range(Cells(i, 1), Cells(i + 45, 1 + 5))
   source.Copy
        With wdDoc
            wdDoc.PageSetup.LeftMargin = "0.5"
            wdDoc.PageSetup.RightMargin = "0.5"
            wdDoc.PageSetup.TopMargin = "0.75"
            wdDoc.PageSetup.BottomMargin = "0.75"
            'Paste the source-range into the active document as a picture
            wdApp.Selection.Range.PasteSpecial Link:=False, DataType:=3, _
            Placement:=wdInLine, DisplayAsIcon:=False
        End With

Next i
End With

Any help would be wonderful!!

Thanks!

 

[Gavona]

So,
a)you want to copy and paste into word all the visible rows in the range A1:F327
b)You want to do this in blocks of 45 rows
c)Does it matter if the first block is the one that is less than 45 rows and the remaining blocks are all 45 rows? Or do you want the last block to be the one with less than 45 rows?

Your questions:
1.There must be a way of modifying your wdDoc code to alter the placement. However I am unfamilar with that so a workaround:

For i = 283 To 1 Step -45

Now you see why I asked (c) above!

2.As I said in my first post, before you start your loop you should copy the visible cells to a new sheet. Run your loop with step 45 on that copy. (I called that range Source2). Delete the sheet at the end. You can record yourself doing all this manually to get the code.
In case you don't know: Select the range and then Edit,Goto,Special,visible cells (or customise your toolbar to include the relevant button)

3.Not sure I understand this. Will Row 327 always be visible and the last row in your range? Do you mean that the 45th row of each block should be row 327? Or what?

Again if you are not familiar there is another very useful technique. Select a single cell in an area. Edit,Goto,Special,CurrentRegion will select the range bounded by blank cells on all sides. (Again, there is a button that can be put on a toolbar if you find this as useful as I do. And of course macro recorder will show you the vba code.

Thanks,

Gavin