Loop through range of cells in a macro?

[cww3]

Hi,

Right now this macro selects cells beginning in C1, C2, and C116. How do I write a loop just for the C2 cells so that subsequent iterations will use C3:BF3, C4:BF4, etc. while maintaining C1:BF1 and C116:BF116?

In other words, the rows that would be copied and pasted would be:

Document 1:
C1:BF1
C2:BF2
C116:BF116

Document 2:
C1:BF1
C3:BF3
C116:BF116

and so on.

Here is the macro:

Range("C1:BF1").Select
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C1:BF1,C2:BF2").Select
Range("C2").Activate
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C1:BF1,C2:BF2,C116:BF116").Select
Range("C116").Activate
Selection.Copy
Workbooks.Add
ActiveSheet.Paste


Thank you for your help!!

 

[SkipVought]

Hi,

"How do I write a loop just for the C2 cells so that subsequent iterations will use C3:BF3, C4:BF4, etc. while maintaining C1:BF1 and C116:BF116?"

I don't know what that means.

dim wsFR as worksheet, wsTO as worksheet
set wsfr = activesheet
workbooks.add
set wsto = activesheet
for i = 1 to 116
  wsfr.range(cells(i,"C"), cells(i,"BF")).copy destination:=wsto.cells(1,"A")
next

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[cww3]

Hi Skip,

What I meant is that I don't want C1:BF1 and C116:BF116 to change. I have 3 rows that are being pasted into new documents, and rows 1 and 3 (C1:BF1) and C116:BF116) never change. Only row 2 (C2:BF2) is supposed to change to C3:BF3, C4:BF4, C5:BF5, etc.

That's not what your code does, right? Thanks again!

 

[SkipVought]

Just change the limits on the loop accordingly.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

"...Only row 2 (C2:BF2) is supposed to change to C3:BF3, C4:BF4, C5:BF5, etc. ."

I don't understand.

What VALUE is etc?

You want row 2 to be copied to multiple rows in the other sheet?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ck1999]

If I am understanding you correctly try this

Sub test2()
Dim wb As Workbook, wbFR As Workbook

Dim wsFR As Worksheet, wsTO As Worksheet

Set wbFR = ActiveWorkbook
Set wsFR = wbFR.ActiveSheet
MsgBox wbFR.Name & wsFR.Name


For counter = 2 To 115  'rows between top an bottom row

Workbooks.Add

Set wb = ActiveWorkbook
Set wsTO = wb.Worksheets("sheet1")
  wbFR.Sheets(wsFR.Name).Range("C1:bF1").Copy Destination:=wsTO.Cells(1, "c")
  wbFR.Sheets(wsFR.Name).Range("C" & counter, "BF" & counter).Copy Destination:=wsTO.Cells(2, "c")
  wbFR.Sheets(wsFR.Name).Range("C116:bF116").Copy Destination:=wsTO.Cells(3, "c")
 wb.SaveAs counter & ".xls" 'for filename
wb.Close
Next counter
End Sub

ck1999

 

[cww3]

Hi ck1999,

Thanks, that was very helpful! Do you know if there's a way for me to save the new files as *.txt files instead of *.xls? I tried just changing it in the code from ".xls" to ".txt" but the text file opens up with a bunch of gibberish.

 

[SkipVought]

SaveAs and choose the appropriate TEXT type.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[cww3]

Hi Skip,

Thanks, but can you explain where/how I would change that? I've never used Visual Basic before. Thank you!

 

[SkipVought]

Turn on your macro recorder, on the sheet File > SaveAs...

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ck1999]

try this


wb.SaveAs counter & ".txt", FileFormat:=xlText, _
CreateBackup:=False

use your macro recorder to do what you want and then from that learn how to manipulate the code


ck1999

 

[cww3]

Thanks everyone! Actually, I had to do was change a line of ck1999's code to:

wb.SaveAs "sample" & counter + 699 & ".txt", FileFormat:=xlText 'for filename

Thanks again