Copying information from several sheets to make a mail merge document

[Mcls2020]

To all the excel master out there,

I have a document and I want to make a sheet that contains all the information that I want to use for mail merge. I can record a Macro to copy the information across but the issue with this is that I end up with lot of blank spaces. I have 8 different sheets to get the information that are labeled A-H. Each contact has two email addresses that are on separate columns that is why I copied the same information over then copied the second lot of email addresses. Here is part of the recorded macro below.

Sub MailMerge()
'
' MailMerge Macro
'

'
Sheets("A").Select
Range("Table2[[Date of Birth]:[Preferred Name]]").Select
Selection.Copy
Sheets("Mail Merge").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("A").Select
Range("Table2[Gender]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mail Merge").Select
Range("E2").Select
ActiveSheet.Paste
Range("F2").Select
Sheets("A").Select
Range("Table2[1st Email]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mail Merge").Select
ActiveSheet.Paste
Sheets("A").Select
Range("Table2[[Column1]:[E City Password]]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mail Merge").Select
Range("G2").Select
ActiveSheet.Paste
Range("A2:N31").Select
Application.CutCopyMode = False
Selection.Copy
Range("A32").Select
ActiveSheet.Paste
Range("E39").Select
Sheets("A").Select
Range("Table2[2nd Email]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mail Merge").Select
Range("F32").Select
ActiveSheet.Paste
Range("A62").Select
Sheets("B").Select
Range("Table3[[Date of Birth]:[Preferred Name]]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mail Merge").Select
ActiveSheet.Paste
Sheets("B").Select
Range("Table3[Gender]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mail Merge").Select
Range("E62").Select
ActiveSheet.Paste
Sheets("B").Select
Range("Table3[1st Email]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mail Merge").Select
Range("F62").Select
ActiveSheet.Paste
Sheets("Mail Merge").Select
Range("G62").Select
Sheets("B").Select
Range("Y2:AF23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mail Merge").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A62:N91").Select
Application.CutCopyMode = False
Selection.Copy
Range("A92").Select
ActiveSheet.Paste
Range("F92").Select
Sheets("B").Select
Range("Table3[2nd Email]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mail Merge").Select
ActiveSheet.Paste
Range("G116").Select
End Sub


Any suggestions welcome.

Thank you

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Mcls2020]

Hi Mumps,

Thank you for your reply. Sorry for taking a long time to reply. Please click on the link below and you should see the file.

文件分享

share.weiyun.com

I have left the formulas in to show where I have taken the information from. The main part is where all the information is gathered in the Tab 'All Classes'. Each sheet it copies it from could have up to 30 entries. I would like these entries to be copied over onto one sheet but do not want to include any blanks.

Thanks again.

 

[mumps]

I'm sorry but the link you supplied is not working. Can you please use www.box.com or www.dropbox.com ?

 

[Mcls2020]

Hope this one works.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[mumps]

By looking at your macro, it looks like you want to populate the Mail Merge sheet with data from the A-H sheets. If this correct, it's hard to tell from your file what data you want to copy from each sheet and how you want it organized on the Mail Merge sheet because the A-H sheets (the H sheet is missing in your file) and the Class Information sheet contain little data. Please add some data (de-sensitized if necessary) to the A-H sheets and the Class Information sheet and then manually add the expected results in the Mail Merge sheet based on that data. You don't have to add 30 rows of data for each sheet. Five or six rows would be enough to give me an idea of how your data is organized and what your desired result should look like. Upload the updated file.

 

[Macropod]

A mailmerge can only reference a single Excel worksheet. That said, a mailmerge can employ a Word DATABASE field to harvest data from multiple worksheets.

For example, a DATABASE field can be used in a normal ‘letter’ mailmerge main document with a macro to drive the process. An outline of this approach can be found at:

Many to one email merge using tables

So, we have decided that we want to communicate with all of our managers across our business, specifically to check the details of their subordinates to ensure that the reporting structure on our HR

answers.microsoft.com

Conversely, if you're using a relational database or, Excel workbook with a separate table with just a single instance of each of the grouping criteria, a DATABASE field in a normal ‘letter’ mailmerge main document could be used without the need for a macro. An outline of this approach can be found at:
Mail Merge - To a Word Table on a Single Page
For some working examples, see:

Mail Merge: Using One Excel File with Multiple Sheets

[Solved] Mail Merge: Using One Excel File with Multiple Sheets Mail Merge

www.msofficeforums.com

using mailmerge to include a grouped information in a letter - Page 2

Page 2-[Solved] using mailmerge to include a grouped information in a letter Mail Merge

www.msofficeforums.com

Merge excel list into Word Receipt

I have an excel spreadsheet that I use for our annual charity auction. The excel spreadsheet has two sheets. One sheet for the guests with the their corresponding paddle numbers. The second sheet is the list of items for sale. I manually enter the paddle number, guest name, and final price next...

www.excelforum.com

(the second of these uses a macro to apply some additional formatting).

The DATABASE field can even be used without recourse to a mailmerge. An example of such usage can be found at: Mail merge into different coloumns