Excel VBA help

[viper37]

I'll use a seperate thread for VBA than for the rest of my (or others) Excel questions.

Now going to VBA.

Code Select Expand


Sub projet_new()
'
' insertion Macro
' insertion d'une nouvelle feuille
'
    Sheets("Prep. acier").Select
    Sheets.Add
    Sheets("Prep. acier").Select
    ActiveSheet.Move _
       After:=ActiveWorkbook.Sheets("acier_pf")
       'Moves active sheet to proper position.

Sheets("Template").Select
    ActiveWindow.SmallScroll Down:=-52
    Cells.Select
    Selection.Copy
    Sheets("Sheet6").Select
    ActiveSheet.Paste
End sub


this is the first part of the macro.

Two problems:
1) I apparently don't need to select an object to do something with it.  How do I do that in my macro?
2) Whenever I insert a new sheet, Excel will change it's name.  Sheet1,  then the next one will be Sheet2, then Sheet3...  So I can't use  Sheets("Sheet6").Select to select my new sheet...  In QP, my  sheet was always "G", so it was easy...

The goal of this part of the macro is to insert a new sheet after the one named Prep. acier, then copy the content from the sheet named Template.

I can not see anything with the Offset property in VBA to make this work, it seems to only apply to rows&columns.
I wish to make this macro as streamlined as possible.  Eventually, it might get big.

[ulmont]

1) I apparently don't need to select an object to do something with it.  How do I do that in my macro?

Why would you need to select an object?  The Sheets object is globally available and contains a reference to all of the sheets in the workbook...

2) Whenever I insert a new sheet, Excel will change it's name.  Sheet1,  then the next one will be Sheet2, then Sheet3...  So I can't use  Sheets("Sheet6").Select to select my new sheet...  In QP, my  sheet was always "G", so it was easy...

Whenever you insert a new sheet, Excel
1) Selects that sheet as the ActiveSheet(meaning you can always use ActiveSheet to refer to your new worksheet).
2) Returns that sheet as the return value of Sheets.Add.  So you could assign the new sheet to some value, ala:

Code Select Expand

Dim newSheet as Worksheet
Set newSheet = Sheets.Add

Alternately, you could set the name right there:

Code Select Expand

Sheets.Add.Name = "my new worksheet"

Also, you can set where the new sheet is to be added, so if you always want to add a new sheet after "Prep. acier", you could do:

Code Select Expand

Sheets.Add After:=Sheets("Prep. acier")

rather than your add, select, move bit.

Finally, the Sheets object has a method to copy a worksheet to a new location, so your whole plan could just be:

Code Select Expand

Sheets("Template").Copy After:=Sheets("Prep. acier")
Sheets("Prep. acier").Next.Name = "This is my new sheet"     'obviously optional if you want to rename the new sheet


[viper37]

Why would you need to select an object? 

'cause I'm not a programmer and I'm totally new to VBA?
I did some coding in BASIC in my younger years.  I learnt Lotus 1-2-3 and Quattro Pro macro command language, but that wasn't object programming.

I still need some adjustement... I'm reading a book, "Excel 2010 Power Programming with VBA", but the first 20% of the book was useless to me (history of spreadsheets, history of programming language, what is a spreadsheet, what is a workbook, etc).

Code Select Expand

Sheets("Template").Copy After:=Sheets("Prep. acier")
Sheets("Prep. acier").Next.Name = "This is my new sheet"     'obviously optional if you want to rename the new sheet


that is exactly what I need!  I'll use the rename part for later
Great! thanks!

[Iormlund]

Why would you need to select an object?  The Sheets object is globally available and contains a reference to all of the sheets in the workbook...

He's probably learning by recording macros and then reading the generated code. Excel does all kinds of stupid crap when you record macros, though, so that is a very bad idea.

[DGuller]

Despite working more than 6 years with all kinds of complciated Excel workbooks, and despite being an experienced programmer in my own right, I'm a complete VBA noob.  I wonder how much I'm missing out on.

[Grey Fox]

Despite working more than 6 years with all kinds of complciated Excel workbooks, and despite being an experienced programmer in my own right, I'm a complete VBA noob.  I wonder how much I'm missing out on.

Do you use Macros in excel?

[DGuller]

Despite working more than 6 years with all kinds of complciated Excel workbooks, and despite being an experienced programmer in my own right, I'm a complete VBA noob.  I wonder how much I'm missing out on.

Do you use Macros in excel?

I use them, I very rarely write them.  Eevn if I do, it's basically record and play back, with hardly any editing of the code.

[ulmont]

Despite working more than 6 years with all kinds of complciated Excel workbooks, and despite being an experienced programmer in my own right, I'm a complete VBA noob.  I wonder how much I'm missing out on.

*shrug*  Depends on if you'd like to do more auto-generation of Excel workbooks, I'd think.  Past that, of course, VBA is Turing-complete (you can even call functions from the general windows API or other DLLs), so you can accomplish just about anything in it (slowly).

[Iormlund]

A word of advice if you need to treat a lot of rows with VBA: instead of doing it the logical way (one row at a time) get all the data into memory, treat it there and then paste the resulting array to the sheet (you can protect the sheet just before pasting so as not to overwrite formulas, margins, etc).
It takes quite a while for Excel to move stuff to and from the sheets, so try to minimize the number of operations that do so.

[viper37]

Duly noted