Real Confusion with a simple macro....

KevMud

New Member
Joined
Oct 8, 2014
Messages
1
Hey guys,

Sorry if this is really simple, but I have literally been up all night trying to get this **** macro to work.

What I have is am Excel spreadsheet, and a Word Document, I have set up a macro button.

I have 4 columns in excel - B20, C20, D20 and E20 - that I need to copy onto the word document. The big issue is it needs to stop when there are no values. Sometimes I will have 2 rows in those columns, sometimes I will have 70.

These columns need to go under a picture that I have on file, with the single cell, D15 also on the word document below everything.

I also what the document to then save and close...

Please help me...
 

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.

Beebs1

New Member
Joined
Oct 8, 2014
Messages
4
Hi

I don't know how this works in terms of putting into a word document, but if you are copying and pasting a range within Excel then you can use the following code which selects the used range within the sheet:

With ActiveSheet.UsedRange.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial
Application.CutCopyMode = False
End With

Hope this helps
 

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
Assuming that B20:E20 have at least 1 blank row above them and that columns A and F are blank, you could say something like:
Code:
If range("B20").CurrentRegion.Cells.Count = 4 then exit sub
Range("B20").Offset(1,0).Resize(Range("B20").CurrentRegion.Rows.Count-1, 4).Copy
That is, if you have one or more rows of data below B20:E20, the CurrentRegion will return a value greater than 4.
So, if there is NO data, CurrentRegion will return 4 and your procedure exits.
If there IS data, the rows of data (but NOT the headers in this example) are copied to the Clipboard.
If you DO want the headers, the command would be:
Code:
Range("B20").CurrentRegion.Copy

Does this help with the first part of your problem?

Pete

P.S. 1000th post - yaay! :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,965
Members
414,114
Latest member
Lost_User21

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