Copy selected cells across to last column

channel7

New Member
Joined
Nov 1, 2007
Messages
47
I have several cells in column B which I would like to copy across under all the columns on the worksheet automatically using a macro.

I currently have a macro which pastes data in from another workbook and the bottom twelve cells in column B are part of a template. I need these twelve cells copied under all the columns which are copied over automatically.

So if four columns are pasted into the worksheet then the twelve cells under column B are then copied under C,D, and E. The twelve cells contain formulas for handling the data in each column.

The twelve cells are B80 through B91. Copied over they will be pasted into C80:C91, D80:D91,...etc.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub test5()
Range("B80:B91").Copy Destination:=Range("C80:E80")
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
To get this to go to the last column. where last column is defined (by me :) ) as the last column with values in row 1

Code:
Sub test5()
Dim LC As Integer
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Range("B80:B91").Copy Destination:=Range(Cells(80, 3), Cells(80, LC))
End Sub
 

channel7

New Member
Joined
Nov 1, 2007
Messages
47
Thanks, VoG II!

That does take care of copying the cells over. Do you know of a way to make the last column variable? Sometimes four columns will be pasted, sometimes only one.

I can hard code the template to have the cells pre-filled across several columns before anything is pasted but I wanted it to look nicer than that for the end users.
 

channel7

New Member
Joined
Nov 1, 2007
Messages
47
Oops! I see you may have already answered my second question as I posted it!

I will check this out and get back with you. Thanks, again for the help!!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Thanks, VoG II!

That does take care of copying the cells over. Do you know of a way to make the last column variable? Sometimes four columns will be pasted, sometimes only one.

I can hard code the template to have the cells pre-filled across several columns before anything is pasted but I wanted it to look nicer than that for the end users.


I think I did that in the code that I posted whilst you were posting your reply. If not, what defines the last column?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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