Google Answers Logo
View Question
Q: Excel/VB Macro Assistance ( Answered 5 out of 5 stars,   2 Comments )
Subject: Excel/VB Macro Assistance
Category: Computers > Programming
Asked by: headless-ga
List Price: $5.00
Posted: 01 Jan 2003 21:42 PST
Expires: 31 Jan 2003 21:42 PST
Question ID: 136274
I created the macro below in Excel (line numbers added for reference):
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

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
(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.



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

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:
I'm trying to get the macro to change its cell references as if I were
dragging it from one cell to another.


Request for Question Clarification by hammer-ga on 02 Jan 2003 10:16 PST
OK, I see what you're after now.

- Hammer
Subject: Re: Excel/VB Macro Assistance
Answered By: hammer-ga on 02 Jan 2003 19:22 PST
Rated:5 out of 5 stars

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

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:

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
3. Paste the macro into that module and Save.

- Hammer
headless-ga rated this answer:5 out of 5 stars and gave an additional tip of: $5.00
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.

Subject: Re: Excel/VB Macro Assistance
From: arrogance-ga on 02 Jan 2003 10:40 PST
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",

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
    ' Range("A3").Select
    Range(arrColumns(intCount) & "1").Select
    Application.CutCopyMode = False
    Range("H" & intCount + 3).Select
    Range("J" & intCount + 2).Select
    Application.CutCopyMode = False
    Range("I" & intCount + 2).Select
    intCount = intCount + 1


End Sub
Subject: Re: Excel/VB Macro Assistance
From: headless-ga on 04 Jan 2003 18:59 PST

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.


Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy