How to make macro start at the Active cell

**livemusic** · 07-07-2011, 09:08 AM

I have numerous sets of data in a spreadsheet. They are all the same number of columns wide, but rows varies. I recorded a macro to do a bunch of things to this data. It appears to be working but it is going back to the original set of data, the original range, and just doing the macro all over again on that data. But I want it to do it on the new set of data, beginning where I have the Active cell. How can I do this? I know the problem is it is selecting A14:K29, which is what I used to record the macro manually, and I don't want it to go there, I want it to deal with the new set of data. (And the number of rows for any given data set will vary with each set.)

Here is the code I got from manually creating my macro.

Please Login or Register  to view this content.

EDIT: For the range, it would be start at the Active cell, go down to the last cell with data (that is not empty), then select all the way over for ten more columns (a total of 11 columns should be selected). And again, the number of rows will vary each time. (The variable is to go all the way down until you get to an empty cell.)

**Domski** · 07-07-2011, 09:20 AM

If the start cell is always A14 try:

Please Login or Register  to view this content.

Dom

**livemusic** · 07-07-2011, 09:27 AM

Originally Posted by Domski

If the start cell is always A14 try:

No, that is the problem, A14 was just the Active cell when I manually created the macro. The first cell will change with each data set.

**Domski** · 07-07-2011, 09:33 AM

Can you give some more info about the actual structure of your data as will need to work out the logic for where the set will end etc. A sample workbook would be a great help.

Dom

**livemusic** · 07-07-2011, 09:43 AM

Originally Posted by Domski

Can you give some more info about the actual structure of your data as will need to work out the logic for where the set will end etc. A sample workbook would be a great help.

Dom

I have attached a sample spreadsheet. With the first range of data, I have done what I wish to be done. Selected the range, sorted, Ascending. Then I went to I1 and made a formula... =h1/30000. Then copied that formula down Column I to the last row. Then formatted that range as a Number with one decimal. Then made a chart of that range.

Below that are two more sample sets, ready to invoke a macro to do the above. The number of rows can vary with each set of data. The spreadsheet will be possibly hundreds of data sets, eventually.

**livemusic** · 07-13-2011, 07:35 AM

Originally Posted by livemusic

I have attached a sample spreadsheet. With the first range of data, I have done what I wish to be done. Selected the range, sorted, Ascending. Then I went to I1 and made a formula... =h1/30000. Then copied that formula down Column I to the last row. Then formatted that range as a Number with one decimal. Then made a chart of that range.

Below that are two more sample sets, ready to invoke a macro to do the above. The number of rows can vary with each set of data. The spreadsheet will be possibly hundreds of data sets, eventually.

Can anyone tell me if this can be done, else, I will forget about it.

**tedwerd** · 11-15-2013, 01:19 PM

I am having the same problem and cant find any help on these forums. A few weeks ago I made what i thought was a very simple sample macro to see if it would do what I want when it came time to do our inventory. Now its that time and I am doing what I believe are the same steps in the recording, but it is starting at the specific cell I recorded in and not whichever cell is selected when the macro starts.

I looked back at the macro I had on my sample file (slightly different, cant use it on the new one) and it doesnt name the specific cells.

All I want is to do a control F(which is another issue, it apparently doesnt work within a macro), select that cell, hit run macro, it will highlight and copy that row, paste it underneath and highlight the new one a different color, then enter a one and highlight the adjacent cell. But today everytime I do it, it is going back to the cell I recorded.

Heres the new one:

Sub Macro3()
'
' Macro3 Macro
'

'
Rows("114:114").Select
Range("C114").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65280
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Copy
Rows("115:115").Select
Range("C115").Activate
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("P115").Select
ActiveCell.FormulaR1C1 = "1"
Range("Q115").Select
End Sub

OLD ONE WHICH DID WHAT I WANTED
' Macro18 Macro
'

'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 0).Range("A1:K1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 12).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

**tedwerd** · 11-15-2013, 02:02 PM

I just noticed the relative references button under the record macro one and that seems to have solved the issue. Only problem now is I'm getting an error that has something to do with activecell.offset.

Sub Macro23()
'
' Macro23 Macro
'

'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65280
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Offset(0, -9).Range("A1").Activate
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveCell.Offset(1, 0).Range("A1").Activate
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(0, 10).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub