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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

lol.xls

Board Regular
Joined
Oct 5, 2009
Messages
174
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.
 

lol.xls

Board Regular
Joined
Oct 5, 2009
Messages
174
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.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Just change the "After" to "Before". :)

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

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,785
Members
414,405
Latest member
Zaurb

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