Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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



  #2   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
external usenet poster
 
Posts: 493
Default Excel table macro

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 ,
"Shiraz Mistry" 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



  #3   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Excel table macro

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" wrote in message
...
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 ,
"Shiraz Mistry" 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





  #4   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default shifting cursor in Excel / VBA macro

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/
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel 2007 macro recorded pivot table getting old Excel Worksheet Functions 2 April 19th 10 09:06 AM
Macro in Excel with Pivot table Martin Excel Discussion (Misc queries) 4 June 23rd 09 08:47 PM
Excel 2007 Macro specific table to generic table? Sue Excel Discussion (Misc queries) 3 June 1st 08 06:12 PM
Create a Word Table from a Excel Macro Silvia Excel Discussion (Misc queries) 0 September 7th 06 12:34 AM
Macro to Extract Text From Word Table Into Excel? V. Hatherley Excel Discussion (Misc queries) 1 August 27th 06 04:12 PM


All times are GMT +1. The time now is 05:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"