Add New Sheet and Copy over data

[peterbread]

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

[Glaswegian]

Hi and Welcome to VBAX !!

Just a couple of small changes, you were virtually there already.

To name the sheet per a cell just use Value. Since you want value and formats use PastSpecial - the default is Paste All.


Sub Add_Sheet()
Dim wSht As Worksheet
Dim shtName As String
shtName = Sheets("Invoice").Range("H11").Value
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").PasteSpecial
Application.CutCopyMode = False
End Sub


HTH

Regards

[peterbread]

Cheers!

That seems to do the trick. Problem is, I've genrated the reference number in a crude manner by using the =RAND()*100000000000 trick to get it to display a random whole number. However, when the new sheet is created the name is often 35385735893.7 instead of 35485735893 and then the reference in that sheet changes to something entirely different.

Any way to implement the reference numbers better? Perhaps in incrementals. I was thinking about adding on code that presses a spinner control to the right everytime it is pressed.

[Glaswegian]

Or perhaps just round the number up?


=ROUND(RAND()*100000000000,0)

or make H11 a round number and add a line in your code to increment


Sheets("Invoice").Range("H11").Value = Sheets("Invoice").Range("H11").Value + 13

Regards

[Jacob Hilderbrand]

You could store the reference number in a cell and just add 1 to it each time your code runs.

If you just want a whole number from your random number, just use Int.


=Int(##)

This will be a whole number.

[peterbread]

Nice one. Thats absoloutley fantastic. I've rounded the reference and added the line of code at the end.

Cheers for the help. Should now get this all finished for Tuesday.


You could store the reference number in a cell and just add 1 to it each time your code runs.

If you just want a whole number from your random number, just use Int.

=Int(##)

This will be a whole number.

I'll keep that in mind if something goes wrong. Should be fine now though.