My first Macro

  • Thread starter Thread starter TJ Buthe
  • Start date Start date
T

TJ Buthe

I am trying to create a macro (in Excel XP/2002) that
copies cells to a new location deletes last column of the
old info and then returns the user to a new blank column.
I can get that much. What I would like it to do after
that is to display an inputbox so that the user is
prompted to enter the current month and year and then
have the information they enter as the header for the new
column. This will always be cell F6. I can get the
InputBox to display but don't know how to get Excel to
put the info the user types into cell F6. This is
probably remedial for you guys, I know but I would really
appreciate any help and can hopefully learn from it.
Here's what I have so far:
ActiveSheet.Unprotect
Columns("F:L").Select
Selection.Copy
Columns("G:M").Select
ActiveSheet.Paste
Columns("M:M").Select
Selection.Delete Shift:=xlToLeft
Range("F5:F6").Select
Selection.ClearContents
Columns("F:F").Select
Selection.ClearContents
Range("F5").Select
InputBox ("Enter the Month and Year, such as Mar 04")

Also, does anyone have book or reference they would
suggest to help me learn VB? I am a beginner so anything
would help. Thanks.
 
Oh, and referring to the book, I use John Greens Excel 2000 VBA Programmer's
Reference (there's a 2002 version now). This is the only one I can
recommend, others talk well of John Walkenbach's but as I have not used it I
cannot comment, but John's is a good book, with a complete Excel Object
Model reference section.
 
TJ,

I'm new to VB too so I don't know the answer to your first question, but can
tell you that I've been using Walkenbach's Power Programming book with some
success. He takes a learning by example and trial and error approach, which
is good for me. It comes with a CD rom that includes examples and a
variety of utility programs.

PC
 
I think Bob copied and pasted your code and left too much in:

This should lose the .select
Columns("F:L").Copy Destination:=Columns("G:M").Select
to:
Columns("F:L").Copy Destination:=Columns("G:M")

And I bet you've noticed that when you copy and paste, you can select a giant
range and just paste to a single cell. VBA allows you do to that to. (I don't
like matching number of columns/rows. I'll just let excel do the work.)

Columns("F:L").Copy Destination:=range("g1")
 
Too many John's!!!!

I like both, but I think that John's (Walkenbach) might be easier to start
learning VBA with. But I'd visit my local book store and see which I liked
better. After a little time, you might end up with both.

In fact, Debra Dalgleish has a big ole list at:
http://www.contextures.com/xlbooks.html
 
Back
Top