Some basic help for a newbie

[druid9855]

Hi all!: I taught myself how to write macros in Lotus a long time ago and now I need to learn it in Excel, but I'm an old dog trying deperately to learn new tricks (and this VBA stuff is waaay beyond the little bit of BASIC I learned a million years ago!). I've searched for this stuff, but I guess it's too basic. Please help!
I have a fairly simple schedule laid out and want to automate it as follows:
1. Move the cursor to a cell named SET (that is the first cell of a row numbered 1-6)
2. Pause, prompt the user to move the cursor to their choice of cell #1-6 (denoting the first day of a new schedule), and then resume the macro (I would guess after pressing ENTER).
3. Check that the cursor is in a cell numbered 1-6; if not, go back to Step 1.
4. Move the cursor down 1 row, anchor the cursor and select (for copying) everything below that point (end down) and to the right the number of columns specified in a cell named MDAYS (the number of days in a month)(copy a schedule block).
5. Copy that block and paste it beginning at a cell named CAL (on another worksheet page).
6. Release all selected blocks and return the cursor to the upper left cell on each page (named CALENDAR and WORKSHEET).
Fudging around, I have figured out a couple of these things, but I don't really know how to put it all together. I realize this is kindergarten stuff, but I am grateful for any help and hopeful that seeing how it's done will guide me down the line. Thanks!

 

[onlyadrafter]

Hello,

Have done it (I think) how is it working?

Sub macro1()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Range("SET").Select
ENTRY:
        If MY_CHOICE < 1 Or MY_CHOICE > 6 Then
            MY_CHOICE = InputBox("Please enter 1-6", "PLEASE SELECT")
                If MY_CHOICE < 1 Or MY_CHOICE > 6 Then
                    MY_ENTRY = MsgBox("PLEASE RE-ENTER", vbOKOnly, "ERROR")
                    GoTo ENTRY
                End If
        End If
        Selection.Offset(MY_CHOICE, 0).Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.Offset(0, Range("MDAYS").Value)).Copy
        Sheets("Sheet2").Range("CAL").PasteSpecial (xlValues)
        Application.CutCopyMode = False
        Sheets("CALENDER").Range("A1").Select
        Sheets("WORKSHEET").Range("A1").Select
        Application.ScreenUpdating = True
End Sub

 

[druid9855]

Thanks, Only, One problem: For the ENTER and SELECT section, I don't need to enter a number 1-6; I need to be able to move the cursor.

>....D|E|F|G|H|I|J
4....1|2|3|4|5|6|7
5....X|6|6|6|6|X|X

Cell D4 is named SET. I need the cursor to physically go there, and the prompt to allow the user to move the cursor anywhere between there and cell I4 (#s 1-6), then check that the cursor is not in J7 or beyond. If OK, the cursor should drop down the same column to row 5 and begin the copy/paste procedure.
So far, everything is looking pretty good though, and I'm beginning to get it (just a little bit!), so I will also be playing with it. Thanks!

 

[onlyadrafter]

Hello,

DOn't know if this is exactly what you want, and haven't thoroughtly tested, so let me know how you get on.

THis code goes into the relevant sheet code window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
    If ActiveCell.Address <> "$D$4" And Range("J1").Value = "NO" Then Range("J1").Value = "NO"

  If Range("J1").Value = "START" And ActiveCell.Row = 4 And ActiveCell.Column > 3 And ActiveCell.Column < 10 Then
        Selection.Offset(1, 0).Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.Offset(0, Range("MDAYS").Value)).Copy
        Sheets("Sheet3").Range("CAL").PasteSpecial (xlValues)
        Application.CutCopyMode = False
        Sheets("CALENDER").Range("A1").Select
        Sheets("WORKSHEET").Range("A1").Select
        Application.ScreenUpdating = True
    End If
End If
End Sub

and this code goes into a standard module

Sub GOTO_SET_CELL()
    Sheets("Sheet2").Range("SET").Offset(-1, 0).Select
    MY_ENTRY = MsgBox("Please select a cell from D4 to I4", vbOKOnly, "CELL SELECT")
    Range("J1").Value = "START"
End Sub

Do you need an explanation?

 

[druid9855]

Only, Thanks for the update, but rein in for a sec...I'm still dizzy. What do you mean by "THis code goes into the relevant sheet code window"?

 

[onlyadrafter]

Hello,

I assume you know how to put the code in a standard module.

When you are in the VB window, you may have a window similar to Windows Explorer, if not select VIEW --> PROJECT EXPLORER, in here, it will have a list of the spreadsheets you have open

e.g. VBAProject(Book1)

click the + next to this to show a -

you should see Microsoft Excel Objects, and a list of the TAB names, e.g. Sheet1, Sheet2 etc,

double click on the sheet with the correct name, the main window will change and show (General) and (Declarations) at the top. THis is where the code needs to go (the bigger piece of code).