Looping Macro

[RCarlson]

HI, I am brand new to writing macros and am just learning on the fly right now. I recorded a macro that I am now trying to loop. I will have up to 1000 rows of data that I need to run through my file and produce out put from. Below is the macro I currently have:

Rows("3:3").Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Variance").Select
Application.CutCopyMode = False
Sheets("Variance").Copy Before:=Sheets(1)
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Output").Select

End Sub

This is taking Row 3 on my "Output" tab and copying it to Row 2. Row 2 then feeds calculations in the file and produces a "Variance" tab which I am copying as a new tab and pasting values only. I then go back and copy Row 4 on the "Output" tab and paste it in Row 2- calculations run - I get my variance output - save it and then go on down the list. I've been able to modify the code to work EXCEPT I can't get it to move from Row 3 to Row 4 and so on. I know this is probably something obvious, but I'm struggling. I pasted the clean "recorded" code above instead of my attempts. Thanks!

 

[Norie]

What range do you want to copy/paste special values on the sheet Variance?

 

[RCarlson]

I am copying the whole tab. When I recorded I right-clicked, selected "copy or Move" and then created a copy. I may change this in the future - but for now am just trying to get it to work. When I run the macro I wrote it will create the multiple variance sheets - but they all have the same data.

What range do you want to copy/paste special values on the sheet Variance?

 

[Norie]

But on the tabs you are copying what range do you want to copy/paste special?

That's not clear from the code because all you have is this.

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

What is Selection referring to here?

 

[RCarlson]

The first time it is in the code I am copying Row 3 on the output tab and pasting it in row 2. So once this runs through once the whole process I want it to go down to row 4, copy the whole row and paste in row 2, then row 5 and so on. I will have an unknown number of rows.

The second time you see that code I am copying all data on my new variance tab and pasting values only.

 

[Norie]

I understand that part, it's the copying and pasting on the 'Variance' sheet I'm wondering about.

Here's the basic code for the looping part, I've added code to copy and paste special every cell on the new sheet.

Dim rng As Range

    Set rng = Sheets("Output").Rows(3)

    Do 

        rng.Copy 
        Sheets("Output").Range("A2").PasteSpecial xlPasteValues

        Sheets("Variance").Copy Before:=Sheets(1)

        ' copy and paste special all 104,857,616,384 cells on new worksheet
        Sheets(1).Cells.Copy
        Sheets(1).Range("A1").PasteSpecial xlPasteValues

        Set rng = rng.Offset(1)

    Loop Until rng.Cells(1,1).Value = ""

 

[RCarlson]

Thank you so much!!!