Mail Merge WIP

[Treguard]

Hi guys

I'm a complete newbie to macros and visualbasic, however I'm basically working on a formula that will correct a vlookup and produce a mail merge, then print the letters (from a specific tray (Tray 2 - letterhead) if that's possible. Before I've even got to that point though, I keep getting a Debug error on Line 13.

If anyone could look at the code below to see why it's flagging it up that would be really appreciated.

Sub Copy_and_Paste_Special()
'
' Copy_and_Paste_Special Macro
' Macro recorded 01/11/2010 by XXXXXX'
'
Range("B2:J100").Select
ActiveWindow.SmallScroll Down:=-81
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
For Each x In Range("E2:I70")
x.Value = Application.Proper(x.Value)
Next
For Each cell In Range("F2:I70")
If cell = "0" Then
cell.Value = ""
End If
Next
End Sub





Thanks

 

[Macropod]

Hi Treguard,

There's nothing inherently 'wrong' with your code that I can see.

Having said that, though, I wonder why you're bothering. A mailmerge can handle data that includes values derived by formulae, so there's no need for the 'paste special' portion of your code on that account. Similarly, with a bit of field coding in Word, you can change the case of the mailmerge data and, for numeric data, suppress the output of zeros. In other words, for what you're apparently trying to do, there's no apparent need to change the source data at all.

As for the code, you could simplify it to:

Sub Copy_and_Paste_Special()
Dim cell As Range

For Each cell In Range("B2:J100")
    cell.Value = cell.Value
Next
For Each cell In Range("E2:I70")
    cell.Value = Application.Proper(cell.Value)
Next
For Each cell In Range("F2:I70")
    If cell.Value = "0" Then cell.Value = ""
Next
End Sub

Even this could probably be simplified, but without knowing more about the data, it's hard to say.

 

[Treguard]

Hi Macropod.

Without going into too much detail about the data source (it's basically employee data). Here's the process:-

Post sent out logged on spreadsheet 1 (Items sent to Employee)
Copy national insurance numbers from post sent out that day from Spreadsheet 1 to Spreadsheet 2 (Mail merge Spreadsheet).
Vlookup looks at an 'All Employees spreadsheet' to pull the name and address details.
Copy/Paste special, save spreadsheet as Book1.xls on desktop.

I then do a mail merge in Word using Book1 (as to not affect the original mail merge spreadsheet forumlas) and print off to Tray 2.

 

[Macropod]

Hi Treguard,

So why not use the VLOOKUP results on Spreadsheet 2 directly? There's no reason I can see for anything involved with the mailmerge to 'affect the original mail merge spreadsheet forumlas'.

 

[Treguard]

Hi Macropod. I asked the same question myself (apologies for the spelling mistake!). I suppose the only reason that they haven't (I only came into the role in July) just put it all on the same ssheet might be due to the number of rows. All of 2010s 'items of post sent out' are marked on Sheet1 in date order. At the moment we're on 6500 rows.

That could be the reason why, although I'm not 100% certain.

 

[Treguard]

Couldn't edit my last post sorry. I noticed in your formula that you didn't put End If. If this not always necessary when using IF?

 

[Macropod]

Hi Treguard,

The number of rows in a mailmerge source is of little consequence. If the source contains rows that you don't want to include, you can either use the mailmerge filter or a SKIPIF field to exclude them.

As for the 'If' test in the macro, you don't need the 'End If' when the complete statement spans only one logical line.