PDA

View Full Version : VBA to copy worksheet and insert at the end all worksheets



nirvehex
09-17-2014, 01:34 PM
Hi,

I have one worksheet named "Bid Template". I'm trying to write a macro that makes a copy of this worksheet, puts the tab at the end of all my other worksheets within the same workbook and renames it "Bid Scrubbed".

Then I want the same macro to insert 4 columns starting at column DV, shifting this column to the right. I want to name the columns "Test 1", "Test 2", "Test 3", "Final Test".

Then I want to paste the entire sheet as Paste Special Values.

Is this a difficult code to write? Can anyone help me out? When I try to record a macro, it breaks for some reason.

Thanks!

westconn1
09-18-2014, 02:10 AM
try like


sheets("bid template").copy ,sheets(sheets.count)
set sht = sheets(sheets.count)
sht.name = "Bid Scubbed"
sht.range("du:dx").entirecolumn.insert
sht.range(dv1:dy1").value = array("test 1", "test 2", "test 3", "final test")
i did not test this, so may contain code error or typos
if the inserted columns are not quite right, adjust the ranges to suit


Then I want to paste the entire sheet as Paste Special Values.
i am not sure what you want with this as everything is already copied in the sheet