Copy several ranges to Word with VBA, based on a list

eivstei

New Member
Joined
Aug 27, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi.
I want to copy several ranges in a workbook to one Word document. How do I do that with a VBA?
In this example, I have defined the ranges with names. In "sheet3", I want range F3:F20 to be cells that users can define which named ranges they want to export to Word.

This code does work if the named range is in the same sheet as the button. But I need it to work when the named ranges are in another sheets.
I also need this code to copy next named range valued in the F4 cell, and down to end of list.

I appreciate your help

________________________________________
Private Sub CommandButton3_Click()
Dim WordApp As Word.Application
Dim WordDoc As Word.Document

Set WordApp = New Word.Application
Set WordDoc = WordApp.Documents.Add


Application.Range(Range("F3").Value).Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
WordApp.Visible = True

Set objSelection = WordApp.Selection
objSelection.Paste
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I will provide a little more information that sheds light on the issue

I have a workbook template with closing accounts. Each month, several areas of this workbook will be copied into Word for presentation and printing.
To make copying more efficient, I want a VBA that copies the areas by the touch of a button. It should be possible for my colleagues to edit the areas to be copied, even if they have no knowledge of VBA. Therefore, I want the VBA to copy areas based on the values in the cells F3:F20. I do not know if it is best to define an area name or location (for example sheet1! B2: G10) in the F coloumn.

The code I have written will copy the named range valued in F3, and paste it as a picture in a new word document.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top