Excel table macro

Hi all,

I want to position my cursor in a cell (in Column A) and press a macro
button to fill in the rest of the week.

For example, cell A91 should fill the rest of the week as the above week,
that is, A81:H89.

Currently, this is the code for the table-

Sub Table()
'
' Table Macro
' Macro recorded 6/24/03 by Shiraz Mistry
'
'
Range("A71:H79").Select
Selection.Copy
Range("A81").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
I know it has something to do with the above range:

Range("A81").Select

but I am not sure what and how to change this line.

I can send the workbook to people that may be interested.

Any help would be appreciated,

Thanks,

Shiraz J. Mistry

One way:

Public Sub Table()
With ActiveCell
If .Row > 10 Then _
.Offset(-10, 0).Resize(10, 8).Copy Destination:= _
.Cells
End With
End Sub

This uses the active cell, and, as long as it's in Row 11 or higher,
copies the 10 rows above it (and 8 columns wide) to the activecell.

The "." are shorthand for the object that follows the "With" (i.e.,
ActiveCell).

Notice that it avoids making any Selections - using the Range
objects directly (in this case ActiveCell and the ranges derived
from it) makes your code smaller, faster, and IMO easier to maintain.


BTW - most regulars read .misc, .worksheet.functions and
..programming, so, while cross-posting isn't nearly as bad as posting
individual messages to different groups, it's largely not necessary.



In article <3effdfa3$0$26637$afc38c87@news.optusnet.com.au>,
"Shiraz Mistry" <smileysjm@optushome.com.au> wrote:

> Hi all,
>
> I want to position my cursor in a cell (in Column A) and press a macro
> button to fill in the rest of the week.
>
> For example, cell A91 should fill the rest of the week as the above week,
> that is, A81:H89.
>
> Currently, this is the code for the table-
>
> Sub Table()
> '
> ' Table Macro
> ' Macro recorded 6/24/03 by Shiraz Mistry
> '
> '
> Range("A71:H79").Select
> Selection.Copy
> Range("A81").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> End Sub
> I know it has something to do with the above range:
>
> Range("A81").Select
>
> but I am not sure what and how to change this line.
>
> I can send the workbook to people that may be interested.
>
> Any help would be appreciated,
>
> Thanks,
>
> Shiraz J. Mistry
>
>
>

Thank you. Again, I am totally in awe of your quick answer. It does work and
I am understanding the code with your explanations.

Also thanks for the kind reminder of cross posting. I will remember this in
the future.

Shiraz J. Mistry

"J.E. McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-859541.01111430062003@msnews.microsoft.com...
> One way:
>
> Public Sub Table()
> With ActiveCell
> If .Row > 10 Then _
> .Offset(-10, 0).Resize(10, 8).Copy Destination:= _
> .Cells
> End With
> End Sub
>
> This uses the active cell, and, as long as it's in Row 11 or higher,
> copies the 10 rows above it (and 8 columns wide) to the activecell.
>
> The "." are shorthand for the object that follows the "With" (i.e.,
> ActiveCell).
>
> Notice that it avoids making any Selections - using the Range
> objects directly (in this case ActiveCell and the ranges derived
> from it) makes your code smaller, faster, and IMO easier to maintain.
>
>
> BTW - most regulars read .misc, .worksheet.functions and
> .programming, so, while cross-posting isn't nearly as bad as posting
> individual messages to different groups, it's largely not necessary.
>
>
>
> In article <3effdfa3$0$26637$afc38c87@news.optusnet.com.au>,
> "Shiraz Mistry" <smileysjm@optushome.com.au> wrote:
>
> > Hi all,
> >
> > I want to position my cursor in a cell (in Column A) and press a macro
> > button to fill in the rest of the week.
> >
> > For example, cell A91 should fill the rest of the week as the above
week,
> > that is, A81:H89.
> >
> > Currently, this is the code for the table-
> >
> > Sub Table()
> > '
> > ' Table Macro
> > ' Macro recorded 6/24/03 by Shiraz Mistry
> > '
> > '
> > Range("A71:H79").Select
> > Selection.Copy
> > Range("A81").Select
> > ActiveSheet.Paste
> > Application.CutCopyMode = False
> > End Sub
> > I know it has something to do with the above range:
> >
> > Range("A81").Select
> >
> > but I am not sure what and how to change this line.
> >
> > I can send the workbook to people that may be interested.
> >
> > Any help would be appreciated,
> >
> > Thanks,
> >
> > Shiraz J. Mistry
> >
> >
> >

Folks,

I recieved the following query from my gf this morning - and have no idea
how it is done. She's recently change job, old textbooks are in transit
and new employer hasn't set her up for newsgroups.

All help appreciated - if you are posting can you also mail me please -
clarke w rice At yahoo dot co dot uk

Thanks

---
I'm fiddling with Visual Basic [Excel] and I now don't have my trusty VB
textbook I had in old office and help files aren't installed in this
computer.

What I'm trying to do is have a combobox linked to a cell and then have a
command box I click on and whatever name is in linked cell I'm takien to a
certian cell within spreadsheet eg If A12 = Mike then take me to cell A20.

I canget the IF = bit, but I'm having trouble with 'take me to this cell
bit' and as help files aren't installed here can u have a alook for me on
your PC and let me know? Ta muchly !





--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/