Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro editing
I am trying to create a Macro where I select a name from a filtered list I
created. I need the Macro to select the next row or name in the filtered list each time I activate my Macro. This filtered list has data attached to the names and the filter selects all the numbers I need to be pasted into a worksheet titled "Totals". |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro editing
Do it manually with the macro recorder turned on, this will generate the
basic code. Then just edit it for flexibility. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Skeeter" wrote in message ... I am trying to create a Macro where I select a name from a filtered list I created. I need the Macro to select the next row or name in the filtered list each time I activate my Macro. This filtered list has data attached to the names and the filter selects all the numbers I need to be pasted into a worksheet titled "Totals". |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro editing
Hi,
And Run it as much as you want Regards, Tim "Bob Phillips" wrote: Do it manually with the macro recorder turned on, this will generate the basic code. Then just edit it for flexibility. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Skeeter" wrote in message ... I am trying to create a Macro where I select a name from a filtered list I created. I need the Macro to select the next row or name in the filtered list each time I activate my Macro. This filtered list has data attached to the names and the filter selects all the numbers I need to be pasted into a worksheet titled "Totals". |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro editing
Thanks Bob for your quick response! I did run the Macro manually and it does
essentially what I want it to do except that when I run the Macro the second time it just repeats and enters the same name and values again in the same worksheet at the same location. I want the Macro to continue down my list and insert each new name and values one after the other. My problem is I don't know how tot tell the Macro in the editing window of VBE how to perform this task. "Tim" wrote: Hi, And Run it as much as you want Regards, Tim "Bob Phillips" wrote: Do it manually with the macro recorder turned on, this will generate the basic code. Then just edit it for flexibility. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Skeeter" wrote in message ... I am trying to create a Macro where I select a name from a filtered list I created. I need the Macro to select the next row or name in the filtered list each time I activate my Macro. This filtered list has data attached to the names and the filter selects all the numbers I need to be pasted into a worksheet titled "Totals". |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro editing
to get the last row or column you need to use the END method. there is two
form of the method, one for rows and one for columns LastRow = cells(Rows.count,"A").end(xlup).row Row.count = 64536 the last row on the worksheet. XLUP tells excel to move up the worksheet until it finds a non empty cell. You can use any colymn in place of "A" LastColumn = cells(1,Columns.count).end(xltoleft).column Columns.count = 256 the lat column in the worksheet. XLTOLEFT tells excel to go left until it finds a non-empty cell 256 and 64536 are the limits in excel 2003. Ecel 2007 has an extended range. "Skeeter" wrote: Thanks Bob for your quick response! I did run the Macro manually and it does essentially what I want it to do except that when I run the Macro the second time it just repeats and enters the same name and values again in the same worksheet at the same location. I want the Macro to continue down my list and insert each new name and values one after the other. My problem is I don't know how tot tell the Macro in the editing window of VBE how to perform this task. "Tim" wrote: Hi, And Run it as much as you want Regards, Tim "Bob Phillips" wrote: Do it manually with the macro recorder turned on, this will generate the basic code. Then just edit it for flexibility. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Skeeter" wrote in message ... I am trying to create a Macro where I select a name from a filtered list I created. I need the Macro to select the next row or name in the filtered list each time I activate my Macro. This filtered list has data attached to the names and the filter selects all the numbers I need to be pasted into a worksheet titled "Totals". |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro editing
Good point!!!
Not bad idea also to use a loop. Have a look at this post and change the code to your needs: Just to let you know you can stop a macro from either the worksheet or VBA by typing CNTR-BREAK. You can have the code continue by going to VBA and typing F5. Here is the code you are looking for: Sub ContinueProcessing() Const Lastrow = 200 Const LastColumn = "D" Const ColANumber = 1 Dim ColLetter As String If IsEmpty(Range("E1").Value) Then Cells("E1") = "A1" LastCell = Range("E1").Value ColLetter = "" Do While Not IsNumeric(Left(LastCell, 1)) ColLetter = ColLetter & Left(LastCell, 1) LastCell = Mid(LastCell, 2) Loop RowNumber = Val(LastCell) StartCol = ColLetter For RowCount = RowNumber To Lastrow Set ColumnRange = Range(Cells(RowCount, StartCol), _ Cells(RowCount, LastColumn)) For Each cell In ColumnRange 'enter your code here ColLetter = ConvertColtoLetter(cell.Column) Range("E1").Value = ColLetter & RowCount Next cell StartCol = "A" Next RowCount End Sub Function ConvertColtoLetter _ (ColNumber As Integer) As String FirstBit = Int(ColNumber / 26) SecondBit = ColNumber Mod 26 If FirstBit = 0 Then ConvertColtoLetter = Chr(Asc("A") + SecondBit - 1) Else ConvertColtoLetter = _ Chr(Asc("A") + FirstBit - 1) Chr (Asc("A") + SecondBit - 1) End If End Function "saman110 via OfficeKB.com" wrote: I have a range of cells like A1:D200 I want a macro that copy and paste A1 in E1 only then you run the macro again and it shows A2 in E1 so on till it reaches D200 and when i close excel and reopen it it should start from where it left off. Please Help thx. "Skeeter" wrote: Thanks Bob for your quick response! I did run the Macro manually and it does essentially what I want it to do except that when I run the Macro the second time it just repeats and enters the same name and values again in the same worksheet at the same location. I want the Macro to continue down my list and insert each new name and values one after the other. My problem is I don't know how tot tell the Macro in the editing window of VBE how to perform this task. "Tim" wrote: Hi, And Run it as much as you want Regards, Tim "Bob Phillips" wrote: Do it manually with the macro recorder turned on, this will generate the basic code. Then just edit it for flexibility. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Skeeter" wrote in message ... I am trying to create a Macro where I select a name from a filtered list I created. I need the Macro to select the next row or name in the filtered list each time I activate my Macro. This filtered list has data attached to the names and the filter selects all the numbers I need to be pasted into a worksheet titled "Totals". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing a macro | Excel Discussion (Misc queries) | |||
Editing a macro | Excel Discussion (Misc queries) | |||
Editing a Macro | Excel Discussion (Misc queries) | |||
Formula Editing Macro? | Excel Discussion (Misc queries) | |||
Editing Macro | Excel Discussion (Misc queries) |