Copy & Paste VBA

[walkerl8]

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,

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

 

[walkerl8]

Hi Mark,

Did you have any thoughts on what we were speaking about regarding saving and recalling data for the other weeks? No worries if not.

Appreciate your time,

 

[MARK858]

I had a quick look the other day but other than setting each number to a variable and then looping through the weeks the only other option I can think of it to put it all into an array.

The looping option won't make it any more efficient (just make the code prettier) and so is really not worth the effort writing the code.

Similarly with the array, which although the array would be more efficient it actually probably would be hardly noticeable to the human eye as the amount of data is very small and so again not worth re-writing the code (not for me anyway).

 

[walkerl8]

Should I just copy and paste what I already have then ?