Copy & Paste VBA

walkerl8

Board Regular
Joined
Apr 30, 2013
Messages
201
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,

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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
I haven't checked the rest of your code but that error means you haven't dimmed your variable when you have Option Explicit at the start of your module. Add the bit in red.
Code:
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, [COLOR="#FF0000"]Rng As Range[/COLOR]
Dim ExNo, Category, Exercise, Sets, Reps, Load As Long
 
Upvote 0
Mark,

It's now highlighting the 'x' and repeating same error 'variable not defined'
 
Last edited:
Upvote 0
IF you mean the x then it should be

Code:
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, [COLOR="#FF0000"]x As Long[/COLOR]
Dim Template As Worksheet, Rng As Range
Dim ExNo, Category, Exercise, Sets, Reps, Load As Long
 
Upvote 0
Mark,

The variable issues seem to be resolved.

The code is now hitting a snag at the highlighted area with an error saying 'cannot do that to a merge cell'

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, x As Long
Dim Template As Worksheet, Rng As Range
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
[B]Rng.Copy[/B]
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
 
Upvote 0
Hi,

Does anyone know why the Run Time Error 1004 - Cannot Do That To A Merge Cell is occurring in the above code? I am not trying to copy from any merged cells and as far as I can tell I am not trying to paste into any. The sheet on I am pasting to has merged cells but shouldnt be involved in any of the code
 
Upvote 0
Afraid I don't use merged cells for exactly this type of reason, hopefully somebody else will be able to shed some light on your issue.
 
Upvote 0
Mark,

It seems that when pasting it is looking down the entire column, therefore sometimes it hits a merged cell (which isn't part of my code) and forces an error. Do you know how to stop this so it only pastes in the section I want it to?
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top