Copy & Paste VBA

W

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
 
Sort by date Sort by votes
@ walkerl8

The rng.Copy you indicate as producing the merged cell error is on the second copy.
The reason... the first paste instruction changed the active sheet to Template because of .Select and .Selection being used.
Instead of
Code: 
Template.Select
Cells(9, 1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
use
Code: 
Template.Cells(9, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
I went with the below code - it pasted the ExNo. and Category but then hit the same merged cell error at the Exercises column paste:

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.Cells(9, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True




''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.Cells(9, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    
''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.Cells(9, 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
        
''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.Cells(9, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    
''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.Cells(9, 6).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    
''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.Cells(9, 8).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
        
End Sub
 
Upvote 0
I have just looked and the Exercise column was over merged cells.

I have used Mark's 'Centre Over Selection' option for that column only and the code is now working.

The 'Centre Over Selection' isn't ideal as I use Data Validation so if there is a work around for that column then that would be awesome.

If not no worries.

I really appreciate both of your help with this - I know it has been a bit of a pain but I am real grateful.

NoSparks said:
@ walkerl8

The rng.Copy you indicate as producing the merged cell error is on the second copy.
The reason... the first paste instruction changed the active sheet to Template because of .Select and .Selection being used.
Instead of
Code: 
Template.Select
Cells(9, 1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
use
Code: 
Template.Cells(9, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
Click to expand...
 
Upvote 0
Happy you got there with the merged cells, what issue are you getting with the validation?
 
Last edited:
Upvote 0
Just that the exercise column is merged over 2 cells so the only way to get the code working is to use your centre over selection. What it does then is put the validation drop down in the middle of the cell so is there no way to get the code to work with those cells being merged ?
 
Upvote 0
Not that I can think of using union. btw what is the purpose of column D?
 
Upvote 0
I think I just merged it for something else on the sheet - are you suggesting that if it isn't required then maybe delete it out and just widen column C so it functions as a single cell?

Also Mark (if you looked at the database on DropBox) - do you see how I have set the code to save and recall all my information including that found in week 1. I also need to try and expand to save and recall the same values from weeks 2/3 and 4. Do you have a simplified way to write the code to get it to do that or is it just a case of copying and pasting the same code I already have and making the necessary changes for those other 3 weeks?

Thanks in advance
 
Upvote 0
and just widen column C so it functions as a single cell?
Click to expand...
Exactly when I looked at the sheet I couldn't see what it was needed for but that doesn't mean I haven't missed it.

Do you have a simplified way to write the code to get it to do that
Click to expand...

I will see if I can have a look at it when I am in tonight.
 
Upvote 0
If you mean lines like

Code: 
    Database.Cells(DR, 6) = Template.Cells(31, 6).Value 'Vol Wk 1
    Database.Cells(DR, 8) = Template.Cells(31, 11).Value 'Vol Wk 2
    Database.Cells(DR, 10) = Template.Cells(31, 16).Value 'Vol Wk 3
    Database.Cells(DR, 12) = Template.Cells(31, 21).Value 'Vol Wk 4

then look for patterns, for instance the above can be written as

Code: 
   destnum = 6
    For mynum = 6 To 12 Step 2
        Database.Cells(DR, mynum) = Template.Cells(31, destnum).Value
        destnum = destnum + 5
    Next

With both destnum and mynum dimmed as Long.

On that subject I suspect that the below isn't declared the way you think it is.

Rich (BB code): 
    Dim ExNo, Category, Exercise, Sets, Reps, Load As Long

In VBA (unlike some other languages) you have to explicitly declare each individual variable or else they are Variant data type and so in the above

ExNo, Category, Exercise, Sets and Reps you are declaring as Variant and only Load is being declared as a Long.

But seeing as you aren't using them any way it doesn't really make any difference :biggrin: Did you leave them there from some old code?
 
Upvote 0
Yeah I think you are right - I just happened to merge those cells during production of the sheet for ease. I will delete that out and widen cell 'C' to solve that problem.

Yeah if you get a chance to look and have any solutions I would really appreciate that.

The code that is in place at the moment is below:

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.Cells(9, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True




''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.Cells(9, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    
''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.Cells(9, 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
        
''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.Cells(9, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    
''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.Cells(9, 6).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    
''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.Cells(9, 8).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
        
End Sub

Where it says recall ExNo. or Category and so forth - you can see on the sheet I am only saving a recalling the data from week one. As I mentioned - I can just paste the above code in again and change the relevant cell numbers, however I didnt know whether there was a way to simplify in terms of saying something like 'recall ExNo. from Weeks 1 then 2 then 3 then 4 and then repeat the same for Category/Exercise/Sets/Reps/Load


​Thanks in advance
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Counter in For Each loop
Replies
2
Views
192
Possum17
P
A
  • Question
Ron de Bruin VBA
Replies
2
Views
960
Alexfoots
A
D
  • Question
VBA optimization advice
Replies
3
Views
93
bobsan42
bobsan42
H
  • Question
VBA Speed up Copy and paste
Replies
3
Views
367
skillilea
S
excelbytes
  • Solved
VBA Code To Copy Data Into Next Blank Column
Replies
5
Views
70
mumps
mumps

Forum statistics

Threads
1,214,806
Messages
6,121,672
Members
449,045
Latest member
Marcus05

Share this page

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