PDA

View Full Version : The sheet selection depends on the variable?



clif
11-09-2011, 01:18 AM
Sub addloop()


For addi = 1 To 1000


ActiveCell.Offset(0, -1).Select
Range(Selection, ActiveCell.Offset(0, 2)).Select
Selection.Copy
Windows("Statament.xls").Activate
Sheets("A").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows("Registration.xls").Activate
ActiveCell.Offset(0, 3).Select
Range(Selection, ActiveCell.Offset(0, 4)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Windows("Statement.xls").Activate
Sheets("A").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Run "statement.xls'!Macro1"
Windows("Registration.xls").Activate
ActiveCell.Offset(0, -2).Select
Selection.End(xlDown).Select


Next addi



End Sub




Since the Sheetname chosen in "statement.xls" depend on variable


If i have the variable { 1= A , 2 = T , 3 = K ...... 1000 = D }
How to insert into above code so that the outcome are following:


Sheets("A").Select for i = 1

Sheets("T").Select for i = 2

Sheets("K").Select for i = 3

Sheets("D").Select for i = 1000


Thanks!

mdmackillop
11-09-2011, 11:47 AM
Try to avoid Activating and Selecting; use a range variables instead.
This may not be correct (its hard to follow without data), but shows how to use an array of sheet names and variable references.
Option Explicit

Sub addloop()


Dim Arr()
Dim addi As Long
Dim Cel As Range
Dim wbStat As Workbook
Dim wbReg As Workbook


Set wbStat = Workbooks("Statement.xls")
Set wbReg = Workbooks("Registration.xls")

Arr = Array("A", "K", "T", "D")

Set Cel = ActiveCell.Offset(0, -1)

For addi = 1 To 1000
Range(Cel, Cel.Offset(0, 2)).Copy
wbStat.Sheets(Arr(addi)).Range("C4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Set Cel = Cel.Offset(0, 3)
Range(Cel, Cel.Offset(0, 4).End(xlDown)).Copy

wbStat.Sheets(Arr(addi)).Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Run "statement.xls'!Macro1"
Set Cel = Cel.Offset(0, -2).End(xlDown)
Next addi
End Sub