Macro to copy and create new sheet

lol.xls

Board Regular
Joined
Oct 5, 2009
Messages
174
I have a workbook where i create "daily reports". I'm trying to find a VBA solution to "copy" the most current report, and create a new tabsheet. (the same way you would right click on the tabe, and select "create a copy". I've tried recording a macro to do this, but the macro keeps trying to copy the same report, and since i have VBA that changes the sheet name automatcially by one numberal, it won't work. Can someone help me with the code so that i can copy and create new sheet tabs based off of the active sheet? Many Thanks for any help on this!!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi there, welcome to the board!

It would help if we saw your code. But to just copy the activesheet, that's fairly simple...
Code:
ActiveSheet.Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.ActiveSheet.Index)

Is that what you're looking for?

HTH
 
Upvote 0
Thanks, this board is amazingly informative.

Sub newrpt()
'
' newrpt Macro
'
'
Sheets("10-15-09").Select
Sheets("10-15-09").Copy Before:=Sheets(1)
Range("U6").Select
Selection.Copy
Range("H6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I8:K8").Select
Application.CutCopyMode = False
End Sub

The prob is it's copying "sheets(1)", when i need it to copy the active sheet.
 
Upvote 0
Hi there, welcome to the board!

It would help if we saw your code. But to just copy the activesheet, that's fairly simple...
Code:
ActiveSheet.Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.ActiveSheet.Index)

Is that what you're looking for?

HTH

it works...but how do i make the new sheets in the book move to the left instead of to the right? for example....my sheets are named by dates, and i'd like the most current dates to be on the far left of the book, and digress to the right.
 
Upvote 0
Just change the "After" to "Before". :)

Edit: Ah, gotcha! Cool beans. :cool:
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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