+ Reply to Thread
Results 1 to 2 of 2

Run-time error '1004' with Range and copy

Hybrid View

  1. #1
    Registered User
    Join Date
    04-10-2007
    Posts
    34

    Run-time error '1004' with Range and copy

    Hi,

    Hopefully this is easy...

    Running the following code I get a Run-time error '1004': Method 'Range' of Object '_worksheet' failed.

    Sub Paste_Observation_probabilities()
    
        Set ws = ThisWorkbook.Sheets("Input Sheet") ' sheet name for locating number of indicators
        ws.Select
    
        numind = Range("C3").Value ' obtaining number of indicators (columns)
        newmaxstat = Range("B22").Value ' total number of observational states
        totstats = Range("B20").Value ' total number of state and observation combinations
    
        Set ws = ThisWorkbook.Sheets("Observation combinations")
        ws.Select
    
        ws.Range(Cells(2, numind * 2 + 1), Cells(newmaxstat + 1, numind * 2 + 1)).Copy
     
        Set ws1 = ThisWorkbook.Sheets("No information obtained")
        ws1.Select
        Range(Cells(2, numind * 2 + 2), Cells(totstats + 1, numind * 2 + 2)).PasteSpecial
        
        ws.Range(Cells(newmaxstat + (2 * 1 + 2), numind * 2 + 1), Cells(2 * newmaxstat + (2 * 2 - 1), numind * 2 + 1)).Copy    
    
    Set ws1 = ThisWorkbook.Sheets("5")
        ws1.Select
        Range(Cells(2, numind * 2 + 2), Cells(totstats + 1, numind * 2 + 2)).PasteSpecial
        
    End Sub
    The problem occurs at the highlighted code, however the first and last cells are referencing the correct cells in the ("Observation combinations") worksheet. The starting and ending cells are dynamically changed via a user input and therefore need to be calculated as shown; also the column is not fixed either.

    Can you help?

    Thanks,

    Mbrolass.

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    Try to modify your code as indicated in bold text:
    Sub Paste_Observation_probabilities()
    
        Set ws = ThisWorkbook.Sheets("Input Sheet") ' sheet name for locating number of indicators
        ws.Select
    
        numind = Range("C3").Value ' obtaining number of indicators (columns)
        newmaxstat = Range("B22").Value ' total number of observational states
        totstats = Range("B20").Value ' total number of state and observation combinations
    
        Set ws = ThisWorkbook.Sheets("Observation combinations")
        ws.Select
    
        ws.Range(ws.Cells(2, numind * 2 + 1), ws.Cells(newmaxstat + 1, numind * 2 + 1)).Copy
     
        Set ws1 = ThisWorkbook.Sheets("No information obtained")
        ws1.Select
        Range(Cells(2, numind * 2 + 2), Cells(totstats + 1, numind * 2 + 2)).PasteSpecial
        
        ws.Range(ws.Cells(newmaxstat + (2 * 1 + 2), numind * 2 + 1), ws.Cells(2 * newmaxstat + (2 * 2 - 1), numind * 2 + 1)).Copy    
    
    Set ws1 = ThisWorkbook.Sheets("5")
        ws1.Select
        Range(Cells(2, numind * 2 + 2), Cells(totstats + 1, numind * 2 + 2)).PasteSpecial
        
    End Sub
    I hope it helps.

    Regards,
    Antonio
    Last edited by antoka05; 06-05-2007 at 05:53 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1