Excel Macro to paste range to any worksheet

Microsoft Office excel 2003
August 3, 2010 at 03:59:13
Specs: Windows XP
I'm really hoping someone can help me here.

I've recorded a macro (code below). The macro basically copies a formatted range and pastes it into the current cell where I'm located on the 'Wireframes' sheet. I want the marco to work on any sheet even if i create a new sheet. I'm guessing I want to take the destination out of the code but my limited knowledge can't work it out in order for the macro to continue working.



Sub Header_One_Col()
' Header_One_Col Macro
' Macro recorded 02/08/2010 by WILLIAKI

Sheets("Headers & Banners").Select
End Sub

See More: Excel Macro to paste range to any worksheet

Report •

August 3, 2010 at 05:13:15

This code will copy the range in Headers & Banners to wherever your active cell is and whatever worksheet it is on:

Sub Header_One_Col()
Worksheets("Headers & Banners").Range("B3:P10").Copy _
End Sub

This is all one line - the underscore with a space before it is a line continuation character.

Note that in Visual Basic it is normally not necessary to Select a cell or other object before acting on it. The macro recorder records these 'selections', but they are not required.

As the only 'fixed' worksheet is Banners & Headings you probably want to store the code with that sheet. Right-click the tab name "Banners & Headings" and select 'View Code'
Paste the code into the Visual Basic window.

You could then attach the macro to a button on a toolbar to make it easily accessible:
Right-click a toolbar and select 'Customize'
From the 'Commands' tab select 'Macros' in the left pane, and then drag the Custom button icon in the right pane to the toolbar.
Right click the new icon, select 'Text only in Menus' and then select 'Assign Macro' and select your macro from the list. Click OK and then close.

Now wherever you are, clicking the button will run the macro and copy and paste that range of cells, starting at the selected (active) cell.


Report •

August 3, 2010 at 05:21:25
That's excellent, works perfectly. Thank you :)


Report •

August 3, 2010 at 05:33:12
You're welcome



Report •

Related Solutions

Ask Question