new sheet adds number from last sheet

i need a code the creates a new sheet but the copies the numbers from the last sheet i created..
joshuajoseph09Asked:
Who is Participating?
 
RobSampsonConnect With a Mentor Commented:
I haven't tested it, but try this.

Regards,

Rob.
dteToday = InputBox("Enter todays date:")
Set objPreviousSheet = ActiveSheet
objPreviousSheet.Copy , ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Set objNewSheet = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
objNewSheet.Name = dteToday
objPreviousSheet.Range("F11:F16,H11:H16,J11:J16,L11:L16,N11:N16,P11:P16,R11:R16").Copy objNewSheet.Range("AA11:AG16")

Open in new window

0
 
FernandoFernandesCommented:
why dont you right click on the sheet tab and select create a copy ?
0
 
RobSampsonCommented:
Hi, what "numbers" are you talking about?  Incrementing the sheet name, or copying data on the sheet?

Rob.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SiddharthRoutCommented:
joshuajoseph09: A sample sheet of Before and After scenario will help for a speedy solution :)

Sid
0
 
joshuajoseph09Author Commented:
i have a button that creates a new sheet, with todays date. i need a code to copy over a row of numbers from the old sheet and put them into the new
0
 
RobSampsonCommented:
So is the old sheet just the sheet immediately before the current one?

Something like this might work:

dteToday = InputBox("Enter todays date:")
ActiveWorkbook.Sheets.Add , ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Set objNewSheet = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
objNewSheet.Name = dteToday
Set objPreviousSheet = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count - 1)
objPreviousSheet.Range("A1:Z1").Copy objNewSheet.Range("A1")


Regards,

Rob.
0
 
joshuajoseph09Author Commented:
i need it more like this. it needs to copy only the active sheet and make a new sheet and copy over the old data
dteToday = InputBox("Enter todays date:")
ActiveSheet.Select.Copy , ActiveSheet(ActiveSheet.Count)
Set objNewSheet = ActiveSheets(ActiveSheet.Count)
objNewSheet.Name = dteToday
Set objPreviousSheets = ActiveSheet(ActiveSheet.Count - 1)
objPreviousSheets.Range("F11:F16,H11:H16,J11:J16,L11:L16,N11:N16,P11:P16,R11:R16").Copy objNewSheet.Range("AA11:AG16")

Open in new window

0
All Courses

From novice to tech pro — start learning today.