PDA

View Full Version : Create New tab



Imdabaum
08-02-2010, 09:14 AM
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.

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

Imdabaum
08-02-2010, 10:03 AM
Found the solution. Now just need to find a copy method that works for the formulae and data.

' 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.

Bob Phillips
08-02-2010, 10:34 AM
Do a copy direct

range_from.Copy range_to

Imdabaum
08-03-2010, 09:56 AM
I get an error Method range of object _Global failed


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


Range(prevTab & ".A1", prevTab & ".L11").Copy(newTab)


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

Imdabaum
08-09-2010, 08:42 AM
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.


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


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.


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


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
08-09-2010, 08:45 AM
What does post #5 have to do with #4?

Imdabaum
08-09-2010, 11:03 AM
Sorry.. tried to clarify post#4 for you.