Posts by jamesparker_1

Hi

The below Vba code runs a mailmerge of data stored in an excel called "mailmerge.xls" fine. However, When it does run it always asks me to chose which sheet I want to mailmerge which is a bit annoying when i want the process to be fully automatic.

ActiveDocument.Mailmerge.MainDocumentType = wdFormLetters
        ActiveDocument.Mailmerge.OpenDataSource  
        Name:="H:\MBI\MailShot\Mailmerge.xls"     
               
     With ActiveDocument.Mailmerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With

What line of code do i need to add to make the macro automatically mailmerge "sheet1" of mailmerge.xls?

Thanks in advance for your help!

James

Hi

I have a text String that I create using the following code in a cell:

=CONCATENATE("WF Tarcker_Edin_",H5," ",RIGHT(G2,2))

where cell H5 is the current month and cell G2 is the year (2006).

So in the cell i end up with the text string : WF Tarcker_Edin_Feb 06. which is the name of another sheet in my workbook containing data that I want to pull into my current sheet. So on my current sheet i want a cell containing the formula:

='WF Tarcker_Edin_Feb 06'!D6

except where 'WF Tarcker_Edin_Feb 06'! is replaced with the whatever string is in the CONCATENATE cell value (Because the month and year values will change and I will therefore want to look at data in different sheets.).

Thanks for your help

Re: Copying Rows using Target in DoubleClick Macros

Absolutely! It worked like a dream! Thank you for your help!

James

Hi

I Have a sheet called "Main" in Which I use a Doubleclick Private function.

When the user Double clicks on a row, I want excel to take note of the row number that was clicked, then move to a different sheet and copy the row in the new sheet (Called "Data") with the same row number as the Target row that was Double Clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


If Target.Column = 4 Then
    Sheets("Data").Select
    Sheets("Data").Rows(Target).Copy
    
    Sheets("Chart Data").Select
    Sheets("Chart Data").Rows("2:2").Paste
    
End If


End Sub

I know The (Target).Copy line is incorrect but I dont know what the correction is.

Re: How to create a Help Section in a Workbook

Hi

I found a free Help file Creater at www.download.com. Just do a search for 'Help File Creator' Then click search. You may need to refine your search to show free programs only. I think the program was called 'Help Creator'.

Quote from Dave Hawley

Right click on the sheet name tab, choose View Code and paste in the code below

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Columns(s)) Is Nothing Then
        Target.EntireRow.Copy _
            Destination:=Sheets("Data").Range("A" & Rows.Count).End(xlUp)(2, 1)
    End If
End Sub

Hi. I have just used the above code and it worked great to copy rows to a sheet called "Data" when I double Clicked on them.

Everything works great now but I think it would be ideal if my main macro could create a new sheet. And then create the above macro in the new sheet to do the same from the new sheet. (i.e. So now I could click on lines in the newly created sheet and the lines would be copied into sheet 'data'.)

I can create a new sheet in a macro but what do you write to create the 'private Worksheet_BeforeDoubleClick' macro on the new sheet?

Re: Exclude Unwanted Data After Macro Filter

Quote from Dave Hawley

Right click on the sheet name tab, choose View Code and paste in the code below

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Columns(s)) Is Nothing Then
        Target.EntireRow.Copy _
            Destination:=Sheets("Main").Range("A" & Rows.Count).End(xlUp)(2, 1)
    End If
End Sub

Hi Again. I Used the above code and it worked great. (Although I did have to remove the If Not Intersect... line to make it run - Just so i can learn, Can you tell me what this line would normally do?)

Everything works great now but I think it would be ideal if my macro could create a new sheet. And then create the above macro to do the same from the new sheet. (i.e. So now I could click on lines in the new sheet and the lines would be copied into sheet 'Main'.)

How do you write that?

Hi

I have used the "Record Macro" Function to write the below code in word. I then copied it to Excel cause I want excel to open word and create the mailmerge word document from addresses in the mailmerge.xls file. I have turned on the references to Word VBA scripts in the options and the macro runs to the point of opening word and creating a blank sheet but no further. Please is someone able to spot the flaw in my code? I've marked the place where the 'Debug' tool always highlights if it helps at all.

Sub Open_Word()


Set appWD = CreateObject("Word.Application")
appWD.Visible = True
Documents.Add DocumentType:=wdNewBlankDocument
                                                         


    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:="C:\Documents and Settings\James Parker\Desktop\MBI\Mailmerge.xls" _
    , ConfirmConversions:=False, _
    ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="", SQLStatement1:=""
        
  
    'Add 9 Blank Lines                      'This is where the debug tool highlights!
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.TypeParagraph
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
        , Text:="""RTLNAME"""
    Selection.TypeParagraph
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
        , Text:="""ADD1"""
    Selection.TypeParagraph
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
        , Text:="""ADD2"""
    Selection.TypeParagraph
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
        , Text:="""ADD3"""
    Selection.TypeParagraph
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
        , Text:="""ADD4"""
    Selection.TypeParagraph
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
        , Text:="""POSTCODE"""
    Selection.TypeText Text:="THE MANAGING DIRECTOR"
    Selection.MoveUp Unit:=wdLine, Count:=7
    Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
    Selection.InsertDateTime DateTimeFormat:="dd MMMM yyyy", InsertAsField:= _
        True, DateLanguage:=wdEnglishUK, CalendarType:=wdCalendarWestern, _
        InsertAsFullWidth:=False
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub

Thanks
James

Re: Exclude Unwanted Data After Macro Filter

Hi Guys!

A huge thanks to everyone for helping! So far the above answers worked really well for sorting my information.

One last query before its complete though:

"Flagging" was mentioned above. Can someone tell me how I can do this in a fairly tidy fashion? All I need is to flag rows on a sheet called "main" and then a macro would copy any flaged items to a sheet called "Exclude". What I'd love is to just 'Double Click' on the first cell in a row and that would copy it across. Is this possible?

Thanks

James

Re: Exclude Unwanted Data After Macro Filter

Hi

Apologies, I probably didnt explain myself very well. I'll try and expand to give an example:

I start with a sheet containing addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) that I run a macro on to filter to different sheets based on City. So at this point I have the original Sheet containing all the addresses and then say 3 new sheets are created called "London", "Cardiff" and "Birmingham". At this Point I usually look through each of the filtered sheets to give the data a glance over and I usually find addresses that I would rather had been excluded when the filter was run. For example, John Smith appears in the "London" Filter sheet but I know he has left London but obviously hasnt been removed from the system where I download my Original Address file.

On this occassion I can delete the John Smith row from the london sheet but I know he will reappear next time I download the address file and run my filter macro.

Therefore, what I would like to do is Double click it (or some sort of select) John Smith when I decide I dont want him in my london Filter sheet and this would have the effect of deleting it from the London filtersheet but also copying him to an "Exclusions" Sheet which would filter out all such addresses in my original address download before I run my normal filter macro to filter addresses into relevant cities.

Thanks!

Hi

Basically: I have a sheet for addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) and I run a macro that Filters that data based on the the city to different Tabs. This works fine except that sometimes, once the data is filtered I will find one or two addresses on my new filtered sheets that I really wanted to exclude. As my main address Tab information changes regularly, Ideally, I would like to see the Item on the City filtersheet, Double click it, and this would have the effect of deleting it from the City filtersheet but also copying it to an "Exclusions" Sheet which I could use before I run my normal filter macro to filter out these bad addresses.

Is anyone able to help me find a solution?