Looping across cells, rows, worksheets in VBA

[stata_user]

Hi everyone,

I'm new here (thanks for having me) and am a novice user of Excel. I'm struggling with what I'm sure are basic looping issues in VBA, and am hoping that someone could direct me to a useful instructional website or perhaps be kind enough to help me get started in loops.

Some details: I have approximately 3,000 observations across two worksheets (1990, 2000) that need to be copied into a master file. After they have been pasted into this master file (Sheet 1, values are then adjusted), I need to yet again transpose newly adjusted values into another worksheet (Sheet 3). So, really, I need to repeat the copy/paste process for all 3,00 observations.

In the event anyone finds this helpful, I've pasted one successful iteration of this process, but I'm at a loss of how to loop over this mess.

Any advice is greatly appreciated!

Sheets("1990").Select
Range("A2:R2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("D12:D28").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Sheets("2000").Select
Range("A3:AU3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("I11:I57").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Sheets("2000").Select
Range("AV3:CP3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("J11:J57").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("J177:J222").Select
Selection.Copy
Sheets("Sheet3").Select
Range("B3:AU3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Sheets("Sheet1").Select
Range("K177:K222").Select
Selection.Copy
Sheets("Sheet3").Select
Range("AW3:CP3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True


End Sub

 

[Coding4Fun]

What would this look like if you were to redo the code for the second pass through or second loop?
Meaning would A2:R2 become A3:R3 pasted into Sheet1 D29:D54?

If that makes sense can you draw out what that second pass would look like.

Also will the number you need to loop through change or is it static? Meaning if for example you have a file with 3000 rows to loop through is it always 3000 or is it sometime 2998 or 3002 etc.

In order to map this out you need to identify the range that you want to loop through or where to look for a count and clarify where it should go.

From there I can possibly guide you to a working code.

 

[stata_user]

What would this look like if you were to redo the code for the second pass through or second loop?
Meaning would A2:R2 become A3:R3 pasted into Sheet1 D29:D54?

If that makes sense can you draw out what that second pass would look like.

Also will the number you need to loop through change or is it static? Meaning if for example you have a file with 3000 rows to loop through is it always 3000 or is it sometime 2998 or 3002 etc.

In order to map this out you need to identify the range that you want to loop through or where to look for a count and clarify where it should go.

From there I can possibly guide you to a working code.

Thanks for the prompt reply! To clarify things, I've pasted what 2 unique passes look like. Note that the locations in Sheet1 do not change. If I understand you correctly, there are a constant number of rows to loop through (albeit at different starting points; sheet1990 begins at B2 and ends at B3110, while sheet2000 begins at B3, ends at B3111. Hope this makes sense.

[COLOR=#000000][FONT=Menlo]    Sheets("1990").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("B2:S2").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("D12:D28").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Sheets("2000").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("B3:AV3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("I11:I57").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Sheets("2000").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("AW3:CQ3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("J11:J57").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Range("J177:J222").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Range("B3:AU3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Sheets("Sheet1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("K177:K222").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Range("AW3:CP3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]'Second Pass[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]  Sheets("1990").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("B3:S3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("D12:D28").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Sheets("2000").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("B4:AV4").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("I11:I57").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Sheets("2000").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("AW4:CQ4").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("J11:J57").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=True [/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Range("J177:J222").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Range("B4:AU4").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Sheets("Sheet1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("K177:K222").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Sheets("Sheet3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]     Range("AW4:CP4").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=[COLOR=#011993]False[/COLOR] _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        , Transpose:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]EndSub[/FONT][/COLOR]