Copying information from several sheets to make a mail merge document

Mcls2020

New Member
Joined
Feb 6, 2020
Messages
14
Office Version
  1. 365
Platform
  1. MacOS
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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

New Member
Joined
Feb 6, 2020
Messages
14
Office Version
  1. 365
Platform
  1. MacOS
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.


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.
 

Mcls2020

New Member
Joined
Feb 6, 2020
Messages
14
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Hope this one works.

 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,527
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:
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:
(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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,308
Messages
5,623,895
Members
415,999
Latest member
Sovereign maphoso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top