Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 macro recorded pivot table | Excel Worksheet Functions | |||
Macro in Excel with Pivot table | Excel Discussion (Misc queries) | |||
Excel 2007 Macro specific table to generic table? | Excel Discussion (Misc queries) | |||
Create a Word Table from a Excel Macro | Excel Discussion (Misc queries) | |||
Macro to Extract Text From Word Table Into Excel? | Excel Discussion (Misc queries) |