Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: Copy & Paste VBA

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy & Paste VBA

    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

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,982
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy & Paste VBA

    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, Rng As Range
    Dim ExNo, Category, Exercise, Sets, Reps, Load As Long
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy & Paste VBA

    Mark,

    It's now highlighting the 'x' and repeating same error 'variable not defined'
    Last edited by walkerl8; Feb 12th, 2017 at 02:42 PM.

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,982
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy & Paste VBA

    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, x As Long
    Dim Template As Worksheet, Rng As Range
    Dim ExNo, Category, Exercise, Sets, Reps, Load As Long
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy & Paste VBA

    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
    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

  6. #6
    Board Regular
    Join Date
    Apr 2013
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy & Paste VBA

    Mark,

    Any thoughts? I am not trying to copy into any merged cells.

  7. #7
    Board Regular
    Join Date
    Apr 2013
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy & Paste VBA

    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

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,982
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy & Paste VBA

    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.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    Board Regular
    Join Date
    Apr 2013
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy & Paste VBA

    I am not copying from any merged cells or pasting into any so it doesn't make sense.

  10. #10
    Board Regular
    Join Date
    Apr 2013
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy & Paste VBA

    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?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •