Posts by XenoCode

    It helps if you mention where it errors... most probably the line

    Code
    With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable

    which probably means the document does not contain a bookmark called 'Risk'


    If would probably help if you programmed 'defensively' - check an object exists before trying to use it, especially if you have no control over opening the document. The user may not open the document you expect and then you know exactly what you are dealing with rather than trying to decipher a sometimes obscure error message.


    I would also assign the document object to a variable rather than relying on the ActiveDocument property. Like Excels 'Activesheet', it may not be the one you expect.


    You store the selected file name in strFolder but use FileExplorer as the file name when opening the Word doc. FileExplorer is the File Browse dialogue object not the (string) name of a file.


    You also append a trailing '\' to strFolder so your edit (which is otherwise correct) will also fail. Remove the '\'

    The ActiveDocument object does not have an 'Item' property which is probably the cause of the error. That's all I can say based on the information in your message. Most likely you are replacing information in predefined bookmarks but if you don't say then it's difficult to answer.


    Also this line probably errors too unless 'Christine' is some sort object or variable in scope.

    Code
    If sh1.[B3] = Christine Then

    The majority of this would be to design the templates in the first place and you haven't mentioned how it will be produced - Word, Excel or what.


    If using Word then use Bookmarks to mark the insertion points for the various items of data (the same name in every template), if using Excel use Named ranges to mark where data is to be inserted (Likewise, same names in the different templates). Once that is done develop for the first design.


    It would be simple to loop through a list of BookMarks/Named Ranges inserting data from specific ranges if the Bookmark/Named Range exists in the current template.

    A chart sheet can only have 1 chart - so referencing the properties is a little simpler.


    You get an error in this line in cmdMatGet_Click

    Code
    MatList.RowSource = DataSH.Range("MatOutdata").Address(external:=True)


    The range is empty which causes a 1004 (General) error.


    There is a line above that which is commented out which, I would assume, is used to populate the range

    Code
    'The first range should be the 2 cells the criteria from the userform is written to (the header & search criteria)
       'The second range should be all the headings in the helper columns
       'CriteriaRange :=Range("Materials(CAD021)!$AG$3:$AG$4").CopyToRange:=Range("Materials(CAD021)!$AI$2:$BF$2"), _
        DataSH.Range("B2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=DataSH.Range("$AG$3:$AG$4"), CopyToRange:=DataSH.Range("$AI$2:$BF$2"), _
        Unique:=False


    but I don't really have the time to try and debug/understand it but as you are more familiar with this it might be obvious to you.

    Did you try adding a call to the function if the cell is blank after an edit? If it returns "Horizontal' for the next cell down (Same logic as you adding a break to Row 3 when B2 is edited and not blank) then there is a page break to be deleted.


    This does not handle deleting the page break yet - just trying to prompt you in the right direction.

    I didn't say it can't be done, it just may not be able to be done in the way you expected.



    And there is absolutely no problem in loading 2 documents to the same instance of Word. If you are moving information between them it makes things simpler all around.

    A very rudimentary function to show if a cell is in a row or column that contains a page break (Horizontal or Vertical, just for completeness)


    Code
    Function GotABreak(r As Excel.Range) As String
      
       Select Case True
          Case r.Parent.Rows(r.Cells(1).Row).PageBreak <> xlPageBreakNone
             GotABreak = "Horizontal"
          Case r.Parent.Columns(r.Cells(1).Column).PageBreak <> xlPageBreakNone
             GotABreak = "Vertical"
       End Select
    End Function


    Rudimentary - a cell with both V & H breaks will just report 'Horizontal' - edit to your needs.


    Remember horizontal page breaks occur above a cell and a Vertical to the left... (but you've already covered that with Range("B2") and adding the break before Row 3)

    Your code is difficult to follow. Why create both the WordApp &; objWord objects? They both refer to Word and you only need 1 instance of Word. Not sure, either, why you load one workbook and then add a new blank workbook. You don't have any interaction between them - perhaps there is in code you didn't post, but it's a little confusing as it stands.


    The 2nd code snippet referencing ActiveDocument cannot work in Excel. You need to add a reference to whichever Word object it relates to.


    You also use a Word constant (wdThemeColorText1). This does not exist in Excel unless you have added a reference to Word using the Project/References dialog or else declared it as a variable/constant with it's underlying value (13) in Excel. If you did add a reference to Word, why use late binding (CreateObject) rather than early binding?


    And finally, Word will only display a page background colour in Web view... so maybe it does work after all but you can;t see it with the current view in Word.

    No. There is no dynamic code. VBA is an addition to Excel many years after Excel was first released so the code and the worksheet are 2 entirely separate things.


    The nearest you can get is named ranges. As you add/delete row or columns, the VBA reference to the named range will always refer to the correct cell.

    From your description
    "It designed to put the information from each sheet into columns from left to right.


    I need it to stack the information from the sheets in rows instead"


    .perhaps all you need to do is change the destination for the copy...


    Code
    owb.Sheets("Foot").Range("C15:F17").Copy twb.Sheets("Ball").Cells(1, Rows.count).End(xlUp).Offset(1)

    You can replace special characters in Word using codes like ^p for paragraph marks. So searching for ^p^p and replacing with ^p will remove extra blank lines.


    You don't give an example of your VBA code manipulating the document so something just about straight out of the Word macro recorder: Should be easy enough to include in your existing code.



    Keep in mind this will remove any paragraph formats as well.