Hi All,
Does anyone have a suggestion to help get the following code to work. Debugger is highlighting Rng as variable not defined.
Hopefully the code is understandable but I am just trying to get those cells to select and copy in that worksheet and paste to the other worksheet.
Thanks in advance,
Does anyone have a suggestion to help get the following code to work. Debugger is highlighting Rng as variable not defined.
Hopefully the code is understandable but I am just trying to get those cells to select and copy in that worksheet and paste to the other worksheet.
Thanks in advance,
Code:
Sub Recall_From_Database()
'This code goes to the database and returns a stored entry to the template
'instead of referencing each cell this code copies and pastes three sections
Dim RR As Long 'RR stands for Recall Row - the row in the DB where the entry will be recalled from
Dim Database As Worksheet
Dim Template As Worksheet
Dim ExNo, Category, Exercise, Sets, Reps, Load As Long
Set Database = Sheet4
Set Template = Sheet3
'Find the last
Database.Select
RR = Database.Cells(3, 4).Value
'Recall the Descriptors
Template.Cells(2, 26) = Database.Cells(RR, 3).Value 'Athlete Name
Template.Cells(3, 26) = Database.Cells(RR, 4).Value 'Period
Template.Cells(4, 32) = Database.Cells(RR, 5).Value 'Phase Objective
'
''Recall the ExNo
'select and copy the 21 columns from the database
Set Rng = Nothing
For x = 1 To 21
ExNo = Choose(x, 14, 20, 26, 32, 38, 44, 50, 56, 62, 68, 74, 80, 86, 92, 98, 104, 110, 116, 122, 128, 134)
If x = 1 Then
Set Rng = Cells(RR, ExNo)
Else
Set Rng = Union(Rng, Cells(RR, ExNo))
End If
Next x
Rng.Copy
Template.Select
Cells(9, 1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
''Recall the Category
'select and copy the 21 columns from the database
Set Rng = Nothing
For x = 1 To 21
Category = Choose(x, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, 75, 81, 87, 93, 99, 105, 111, 117, 123, 129, 135)
If x = 1 Then
Set Rng = Cells(RR, Category)
Else
Set Rng = Union(Rng, Cells(RR, Category))
End If
Next x
Rng.Copy
Template.Select
Cells(9, 2).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
''Recall the Exercises
'select and copy the 21 columns from the database
Set Rng = Nothing
For x = 1 To 21
Exercise = Choose(x, 16, 22, 28, 34, 40, 46, 52, 58, 64, 70, 76, 82, 88, 94, 100, 106, 112, 118, 124, 130, 136)
If x = 1 Then
Set Rng = Cells(RR, Exercise)
Else
Set Rng = Union(Rng, Cells(RR, Exercise))
End If
Next x
Rng.Copy
Template.Select
Cells(9, 3).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
''Recall the Sets
'select and copy the 21 columns from the database
Set Rng = Nothing
For x = 1 To 21
Sets = Choose(x, 17, 23, 29, 35, 41, 47, 53, 59, 65, 71, 77, 83, 89, 95, 101, 107, 113, 119, 125, 131, 137)
If x = 1 Then
Set Rng = Cells(RR, Sets)
Else
Set Rng = Union(Rng, Cells(RR, Sets))
End If
Next x
Rng.Copy
Template.Select
Cells(9, 5).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
''Recall the Reps
'select and copy the 21 columns from the database
Set Rng = Nothing
For x = 1 To 21
Reps = Choose(x, 18, 24, 30, 36, 42, 48, 54, 60, 66, 72, 78, 84, 90, 96, 102, 108, 114, 120, 126, 132, 138)
If x = 1 Then
Set Rng = Cells(RR, Reps)
Else
Set Rng = Union(Rng, Cells(RR, Reps))
End If
Next x
Rng.Copy
Template.Select
Cells(9, 6).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
''Recall the Load
'select and copy the 21 columns from the database
Set Rng = Nothing
For x = 1 To 21
Load = Choose(x, 19, 25, 31, 37, 43, 49, 55, 61, 67, 73, 79, 85, 91, 97, 103, 109, 115, 121, 127, 133, 139)
If x = 1 Then
Set Rng = Cells(RR, Load)
Else
Set Rng = Union(Rng, Cells(RR, Load))
End If
Next x
Rng.Copy
Template.Select
Cells(9, 8).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub
