I created the macro below in Excel (line numbers added for reference):
[START MACRO]
1 Sub Macro5()
2    Rows("3:3").Select
3    Selection.Insert Shift:=xlDown
4    Range("A2:G2").Select
5    Selection.Copy
6    Range("A3").Select
7    ActiveSheet.Paste
8    Range("$J$1").Select
9    Application.CutCopyMode = False
10   Selection.Copy
11   Range("H3").Select
12  ActiveSheet.Paste
13   Range("J2").Select
14  Application.CutCopyMode = False
15   Selection.Copy
16   Range("I3").Select
17   ActiveSheet.Paste
18 End Sub
[END MACRO]

I need help adding code that will increment some of the cells and
repeat the code.  For example, the next thing I want it to do is go
back to line 2 and repeat the same steps, but this time starting on
the next row down (row 4).  Also on this second time through, the
range values in lines 4 and 6 also need to have the rows increment by
+1
(so line 4 becomes "Range("A3:G3").Select.")

Basically, I want to start this macro from the active cell and have it
repeat until it hits a blank row and then stop.

Please provide actual code that I can cut and paste into my macro to
make it work.

Thanks!

--H

---

Request for Question Clarification by hammer-ga on 02 Jan 2003 06:47 PST

Could you provide a description of what this macro is meant to do?
There may be better way to shift your values than using cut-and-paste.
We can write a better macro if we know what its purpose is. It looks
like you are picking up chunks of information and moving them over a
cell, down a row, etc.

- Hammer

---

Clarification of Question by headless-ga on 02 Jan 2003 09:24 PST

It's easier to see the spreadsheet than to explain what the macro is
supposed to do.  Please download the sample spreadsheet from
http://www.geocities.com/mattalford2001/

You don't need to run any macros from the spreadsheet, just look at
each worksheet to see the changes that the macro creates.  It is just
copying and pasting information to different locations in the
spreadsheet.  My problem is that when I create a macro in Excel, the
macro is generated using hard cell references.

For example, if I write this formula in Cell A1: =sum(a1:a2)
and then drag A1 to A2, the formula automatically changes in A2:
=sum(a2:a3)
I'm trying to get the macro to change its cell references as if I were
dragging it from one cell to another.

Thanks.

---

Request for Question Clarification by hammer-ga on 02 Jan 2003 10:16 PST

OK, I see what you're after now.

- Hammer

headless,

Based on what I see in your sample spreadsheet, try this macro. It
should do your whole sheet in one pass, rather than having to do a
pass for each row. The code is commented. Please ask for clarification
if you need something explained further. Paste the macro into the code
module for the sheet you want to affect.

Sub PrepForPivot()
Dim intCurrentRow As Integer
Dim intNextRow As Integer
Dim intOuterColumn As Integer
Dim intInnerColumn As Integer

    ' Start at the first row below the headers
    intCurrentRow = 2
    
    ' Run until we hit a blank row
    Do Until Cells(intCurrentRow, 1).Value = ""
        ' Initialize the counter that tells us
        ' how many rows we added
        intNextRow = 0
        ' Check each of the 3 driver columns to
        ' see if we should make a row for it
        For intOuterColumn = 10 To 12
            ' If the cell is marked for that row, make a pivot row
            If Cells(intCurrentRow, intOuterColumn).Value <> "" Then
                ' Insert a blank row
                Me.Rows(intCurrentRow + intNextRow + 1).Insert
                ' Copy the values from the first 7 columns
                For intInnerColumn = 1 To 7
                    Cells(intCurrentRow + intNextRow + 1,
intInnerColumn).Value = Cells(intCurrentRow, intInnerColumn).Value
                Next intInnerColumn
                ' Enter the correct header text in column 8
                Cells(intCurrentRow + intNextRow + 1, 8).Value =
Cells(1, intOuterColumn).Value
                ' Increment the rows added counter
                intNextRow = intNextRow + 1
            End If
        Next intOuterColumn
        ' Move down to the next original row, skipping
        ' the rows we added in code to avoid an endless
        ' loop and lots of incorrectly repeated data
        intCurrentRow = intCurrentRow + intNextRow + 1
    Loop

End Sub


Note: No line of code should wrap.

Additional Resources:
Read the Excel Help for the Cells collection. It shows several
examples of looping through cells in your spreadsheet.

Search Strategy:
None

Good luck with your Excel project!

- Hammer

---

Clarification of Answer by hammer-ga on 02 Jan 2003 19:30 PST

By the way, note that I'm using Value to directly set the cells,
rather than using the Copy and Paste methods.

Also, to make sure you get the code in the right place:
1. From the worksheet you want to affect, press Alt-F11.
2. In the Project panel at the upper left, the Sheet should be
highlighted. In your sample, it's Sheet2(Starting Data). If the
correct sheet is not highlighted, double-click on it to get its code
module.
3. Paste the macro into that module and Save.

- Hammer

Excellent response.  The code gave me exactly what I was after plus
some additional insight into ways that I can approach problems like
this in the future.

While I'm not exactly sure what data you want where (so i'm pretty
sure you'll have to change some of the ranges to suit your needs), one
way of doing this is to create an array that will hold the columns to
read, and then loop through the code you want repeated. You'll also
need to change the format of the column headers. The ranges are just
strings, so you can make them as you go using the counter. The array
is zero based. Sorry for not commenting well, but I ain't getting paid
for this....

Dim arrColumns
arrColumns = Array("J", "K", "L", "M", "N", "O", "P", "Q", "R", "S",
"T")

Dim intCount
intCount = 0
Do Until Range(arrColumns(intCount) & "2").Text = ""
    
    Rows(intCount + 3 & ":" & intCount + 3).Select
    Selection.Insert Shift:=xlDown
    Range("A" & intCount + 2 & ":G" & intCount + 2).Select
    Selection.Copy
    ' Range("A3").Select
    ActiveSheet.Paste
    Range(arrColumns(intCount) & "1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H" & intCount + 3).Select
    ActiveSheet.Paste
    Range("J" & intCount + 2).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("I" & intCount + 2).Select
    ActiveSheet.Paste
    
    intCount = intCount + 1

Loop

End Sub

Arrogance,

Thanks for the code.  It shows me anothe way to get the results I'm
after.  I appreciate you taking the time to help for free.

--H