peterbread
04-24-2005, 12:16 PM
Hi there.
In my project, I want a Macro that creates a new sheet each time it is run. I want it to copy over from Invoice!A1:K33 to A1 in the new worksheet. I picked up this template from the web:
Sub Add_Sheet()
Dim wSht As Worksheet
Dim shtName As String
shtName = Format(Now, "mmmm_yyyy")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists...Make necessary " & _
"corrections and try again."
Exit Sub
End If
Next wSht
Sheets.Add.Name = shtName
Sheets(shtName).Move After:=Sheets(Sheets.Count)
Sheets("Invoice").Range("A1:K33").Copy _
Sheets(shtName).Range("A1")
End Sub
I have edited it to copy over my data, however, I need it to copy just the Values and formatting of each cell in the range. I also want each sheet name to equal the reference number in Invoice!H11, instead of whatever the date and year is at the current time.
How do I go about doing this?
Many Many thanks
In my project, I want a Macro that creates a new sheet each time it is run. I want it to copy over from Invoice!A1:K33 to A1 in the new worksheet. I picked up this template from the web:
Sub Add_Sheet()
Dim wSht As Worksheet
Dim shtName As String
shtName = Format(Now, "mmmm_yyyy")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists...Make necessary " & _
"corrections and try again."
Exit Sub
End If
Next wSht
Sheets.Add.Name = shtName
Sheets(shtName).Move After:=Sheets(Sheets.Count)
Sheets("Invoice").Range("A1:K33").Copy _
Sheets(shtName).Range("A1")
End Sub
I have edited it to copy over my data, however, I need it to copy just the Values and formatting of each cell in the range. I also want each sheet name to equal the reference number in Invoice!H11, instead of whatever the date and year is at the current time.
How do I go about doing this?
Many Many thanks