Help with a Loop

  • Thread starter Thread starter Richard S
  • Start date Start date
R

Richard S

How do I write a macro which loops this ?

Sheets("Data1").Select
Range("C5").Select
Selection.Copy
Sheets("ActivityData").Select
Range("C4").Select
ActiveSheet.Paste Link:=True

Sheets("Data2").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ActivityData").Select
Range("C5").Select
ActiveSheet.Paste Link:=True

Sheets("Data3").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ActivityData").Select
Range("C6").Select
ActiveSheet.Paste Link:=True
 
Richard,

This is untested. But maybe it'll give you an approach

Dim Sheetname as String
Dim SheetNumber as int
Dim LastSheetNumber as Int

SheetNumber = 1 ' starting sheet number
Do
SheetName = "Data" & SheetNumber
Sheets(SheetName).Select
Range("C5").Select
Selection.Copy
Sheets("ActivityData").Select Cells(SheetNumber + 3 ,3)
ActiveSheet.Paste Link:=True
Click to expand...
SheetNumber = SheetNumber + 1
Loop While SheetNumber <= LastSheetNumber
 
Richard said:
How do I write a macro which loops this ?

Sheets("Data1").Select
Range("C5").Select
Selection.Copy
Sheets("ActivityData").Select
Range("C4").Select
ActiveSheet.Paste Link:=True

Sheets("Data2").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ActivityData").Select
Range("C5").Select
ActiveSheet.Paste Link:=True

Sheets("Data3").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ActivityData").Select
Range("C6").Select
ActiveSheet.Paste Link:=True
Click to expand...

Why do you want to loop in VBA rather than simply putting formulas in
Cells C4, C5 and C6 of the "ActivityData" sheet?

Alan Beban
 
Just to add to Alan's reply...

This is one way of doing it:

Worksheets("activitydata").Range("c4:c6").Formula _
= "=" & Worksheets("data").Range("C5").Address(external:=True)
 
Back
Top