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...
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,432
Members
448,573
Latest member
BEDE

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