Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Add new Sheet and Copy data

  Asked By: Cory    Date: Feb 15    Category: MS Office    Views: 1153
  

Found this template for a code that looks quite useful for my project.

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've edited it to copy the data from my "Invoice" sheet, range A1:K33
into a new sheet. However, I need only the values and formatting in
that range and not the formulas. I also need each new sheet name to
equal a random reference number which is in cell H11, instead of
mmmm_yyyy.

How do I go about doing this?

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Sarah Campbell     Answered On: Feb 15

Dim wSht As Worksheet
Dim shtName As String
shtName = Sheets("Invoice").Range("H11")
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 Paste:= _
xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Invoice").Range("A1:K33").Copy
Sheets(shtName).Range("A1").PasteSpecial Paste:= _
xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

 
Answer #2    Answered By: Gloria Cook     Answered On: Feb 15

For your sheet  name as a reference  to cell  H11, see the addition I made
to Pascal's code.

A good way to start finding these answers yourself is to look in the
Help file of the VBA Editor. Start reading up on the Object Model, which
will describe that you have Application, Workbooks, Worksheets, Ranges,
Cells, etc.

And for each of these you can set/read properties, do stuff with them
(Methods in the lingo), and also look for event (e.g. clicking a button,
opening a sheet, pressing Enter. These events can be used to trigger
blocks of code  to execute.

Once you understand the basics of the Object model, you can use "F2" in
the VBA Editor to bring up most of the commands. E.g. what can you do
with sheets?
Press "F2", stay in "All Libraries", and search for "sheets" (without
the quotation marks).

This brings up a lot of information. For what you wanted to do, it could
belong in Worksheets, or Worksheet. And in Worksheet, you find the
Property called Name and Names. Which one do you want? Click on each,
and read the explanation. In this case, Names is read only. So not a
good choice.

The correct command would therefore be:
Worksheet.name = Value.

And thus the logic of the code I added.

 
Didn't find what you were looking for? Find more on Add new Sheet and Copy data Or get search suggestion and latest updates.




Tagged: