Create New tab

[Imdabaum]

I am using the following code to create a new tab. Does anyone have any tips for ensuring that I add the tab at the very end? Currently it forces the tab to be the second to last tab.

Basically it's a menial time sheet, but I'd like to just push a button to start the next week and have the data from the previous tab thrown onto the new tab.

[VBA]Sub createNewWeek()
'Creates a copy of the tab contents and places it on the next tab.

Dim i As Integer, wb As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'To create a new workbook if nothing open, otherwise create a new sheet
On Error Resume Next
Set wb = ActiveWorkbook
On Error GoTo 0
If wb Is Nothing Then
Workbooks.Add
Else
Dim prevTabname As String
prevTabname = CurrentSheetName()
wb.Sheets.Add.Name = CreateNextTabName(prevTabname)
End If
'Autofit columns for easier readability
Columns.AutoFit
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub[/VBA]

[Imdabaum]

Found the solution. Now just need to find a copy method that works for the formulae and data.

[VBA]' This line makes the new sheet (which is also the active sheet) the
' last sheet in the workbook.
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
Application.ScreenUpdating = True ' Enables screen refreshing.
[/VBA]

[Bob Phillips]

Do a copy direct

range_from.Copy range_to

[Imdabaum]

I get an error Method range of object _Global failed


In the current process is it possible to reference the previous tab?

[VBA]
Range(prevTab & ".A1", prevTab & ".L11").Copy(newTab)
[/VBA]

Sorry, I'm not quite familiar with Excel's objects/classes as I've spent most of my time in Access.

[Imdabaum]

Let me clarify this post. I figured out how to copy a range of values from the previous tab over to the new tab. Post 4 references the trouble I was having and the error I got. Below is the code that I used to solve it.

[VBA]
Private Sub CopyPrevTab(prevT As String, newT As String)
'
' CopyPrevTab Macro
'
Sheets(prevT).Select
Range("A1:K11").Select
Selection.Copy
Sheets(newT).Select
Range("A1").Select
ActiveSheet.Paste
Range("B3:H6").ClearContents
End Sub
[/VBA]

Now I am trying to finish manipulating the data that I just copied. ie, delete the old values, and update the dates to current values.

[vba]
Sub UpdateHeaderDates()
Range("H1") = LastDayInWeek
Selection.AutoFill Destination:=Range("B1:H1"), Type:=xlFillDefault
Range("B1:H1").Select
End Sub
[/vba]

It stops working on the Selection.AutoFill function. Each tab contains the dates for columns B1:H1. I already get the date for the end of the week so I figured I would just assign that value to H1, then update the previous cells accordingly. Any help would be appreciated.

I basically created a time sheet with a button. When the button fires, I create a new tab with the next week's date as the tab name, copy formulas/data from previous tab, paste in new tab, delete old values, update the date headers.

[Bob Phillips]

What does post #5 have to do with #4?

[Imdabaum]

Sorry.. tried to clarify post#4 for you.