ajhez
01-11-2016, 08:22 AM
Hi All,
Background: I have an excel sheet containing customer information. Each customer has their own Row in the document with various bespoke data being held about them in each cell along the Row. I currently use a VBA Macro to create letters for the customers - the code works by opening up a template word document for a customer, copying the relevant parts of the data in the Excel sheet for that customer and pasting it into corresponding bookmarked area of the template letter, and then saving the letter in a file using the customer's name. It then moves on to the next Row / customer. The process finishes when all of the Rows / Customers have had a letter generated.
However, there are instances where I would like to produce letters only for a small number of customers - e.g. Customer's begging with the letter 'D'. Ideally I would be able to filter on Customer Name and narrow it down to Customer's begging with D and run the code. Unfortunately any kind of filtering does not affect the code and the processs runs through the full worksheet.
Does anybody know how to amend the code below so that if I was to filter any columns that only the visible cells after filtering would be used rather than all of the active cells in the column? I'm assuming that a change needs to be made to 'ActiveCell' but I can't figure out what that change would be...
Option Explicit
Const MainFilePath As String = "C:\Users\herriale\Desktop\FINAL TT\UK\"
Const FilePath As String = "C:\Users\herriale\Desktop\FINAL TT\UK\Letters\"
Dim wd As New Word.Application
Dim CustomerCell As Range
Sub GenerateLetters()
Dim doc As Word.Document
wd.Visible = True
Dim CustomerRange As Range
Range("B7").Select
Set CustomerRange = Range( _
ActiveCell, _
ActiveCell.End(xlDown))
For Each CustomerCell In CustomerRange
Set doc = wd.Documents.Open(MainFilePath & "UK 2016 Trade Investment - 05.08.2015.docm")
'letteradmin
CopyCell "bmaccountcontactname2", 1
CopyCell "bmaccountaddress1", 10
CopyCell "bmaccountaddress2", 11
CopyCell "bmaccountaddress3", 12
CopyCell "bmaccountcontactname", 9
CopyCell "bmaccountname3", 1
CopyCell "bmaccountmanagername1", 6
Thanks!
Background: I have an excel sheet containing customer information. Each customer has their own Row in the document with various bespoke data being held about them in each cell along the Row. I currently use a VBA Macro to create letters for the customers - the code works by opening up a template word document for a customer, copying the relevant parts of the data in the Excel sheet for that customer and pasting it into corresponding bookmarked area of the template letter, and then saving the letter in a file using the customer's name. It then moves on to the next Row / customer. The process finishes when all of the Rows / Customers have had a letter generated.
However, there are instances where I would like to produce letters only for a small number of customers - e.g. Customer's begging with the letter 'D'. Ideally I would be able to filter on Customer Name and narrow it down to Customer's begging with D and run the code. Unfortunately any kind of filtering does not affect the code and the processs runs through the full worksheet.
Does anybody know how to amend the code below so that if I was to filter any columns that only the visible cells after filtering would be used rather than all of the active cells in the column? I'm assuming that a change needs to be made to 'ActiveCell' but I can't figure out what that change would be...
Option Explicit
Const MainFilePath As String = "C:\Users\herriale\Desktop\FINAL TT\UK\"
Const FilePath As String = "C:\Users\herriale\Desktop\FINAL TT\UK\Letters\"
Dim wd As New Word.Application
Dim CustomerCell As Range
Sub GenerateLetters()
Dim doc As Word.Document
wd.Visible = True
Dim CustomerRange As Range
Range("B7").Select
Set CustomerRange = Range( _
ActiveCell, _
ActiveCell.End(xlDown))
For Each CustomerCell In CustomerRange
Set doc = wd.Documents.Open(MainFilePath & "UK 2016 Trade Investment - 05.08.2015.docm")
'letteradmin
CopyCell "bmaccountcontactname2", 1
CopyCell "bmaccountaddress1", 10
CopyCell "bmaccountaddress2", 11
CopyCell "bmaccountaddress3", 12
CopyCell "bmaccountcontactname", 9
CopyCell "bmaccountname3", 1
CopyCell "bmaccountmanagername1", 6
Thanks!