Run time error 1004

Sherifa

New Member
Joined
Oct 23, 2017
Messages
45
PasteSpecial METHOD OF RANGE CLASS FAILED

Code:
Sub Testing()
Dim Wkb As Excel.Workbook
Dim ws As Sheets
Dim ws_count As Long
Dim i As Long
Dim j As Integer
Dim k As Integer


Set Wkb = ThisWorkbook
Set ws = ThisWorkbook.Worksheets


ws_count = Wkb.Worksheets.Count




'Begin loop
    For i = 1 To 10


            Call RandNumberGen
            Call SimulatedPath
            
            Sheet6.Calculate
            Sheet7.Calculate
            Sheet8.Calculate
            Sheet9.Calculate
            Sheet10.Calculate
            
            


            For j = 1 To 10
    
    


                 For k = 3 To 13
    
            
                    Sheet11.Range("A1:C12").Copy
                    
                    Sheet11.Range(Cells(1, j + 5), Cells(12, k + 5)).PasteSpecial xlPasteValues
                    
                    'Paste Step Column by 5


        Next k


    Next j
Next i


Sheet11.Calculate


End Sub
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
You haven;t fully specified the range, try
Code:
With Sheet11
   .Range("A1:C12").Copy
   .Range(.Cells(1, j + 5), .Cells(12, k + 5)).PasteSpecial xlPasteValues
End With
 

Sherifa

New Member
Joined
Oct 23, 2017
Messages
45
You haven;t fully specified the range, try
Code:
With Sheet11
   .Range("A1:C12").Copy
   .Range(.Cells(1, j + 5), .Cells(12, k + 5)).PasteSpecial xlPasteValues
End With

How did you know that was the solution.


I've adjusted the code. It loops through the first half of my instructions, but doesn't repeat the copy and paste.

Code:
 Sub Testing()Dim Wkb As Excel.Workbook
Dim ws As Sheets
Dim ws_count As Long
Dim i As Long
Dim j As Integer
Dim k As Integer




Set Wkb = ThisWorkbook
Set ws = ThisWorkbook.Worksheets






j = 1
k = 3




'Begin loop
    For i = 1 To 10




            Call RandNumberGen
            Call SimulatedPath
            
            Sheet6.Calculate
            Sheet7.Calculate
            Sheet8.Calculate
            Sheet9.Calculate
            Sheet10.Calculate
            
            


            With Sheet11
                           .Range("A1:C12").Copy
                           .Range(.Cells(1, j + (j * 5)), .Cells(12, k + (j * 5))).PasteSpecial Paste:=xlPasteValues
            End With
                    




  Next i




Sheet11.Calculate




End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
How did you know that was the solution.
Experience, down to having made the same mistake myself on numerous occasions.


With your new code the values of J & K are never changing, therefore you're overwriting the range, every loop.
 

Sherifa

New Member
Joined
Oct 23, 2017
Messages
45

ADVERTISEMENT

Experience, down to having made the same mistake myself on numerous occasions.


With your new code the values of J & K are never changing, therefore you're overwriting the range, every loop.

I changed j = 1 To For j = 1 To 10 and For k=3 to 13, but the loop doesn't work as desired.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
What are you trying to do with that portion of the code?
 

Sherifa

New Member
Joined
Oct 23, 2017
Messages
45

ADVERTISEMENT

The copy section changes after running the part before with and I want the paste, to paste the different values, that is why my range changes. F1:H12 should be the first range. K1:M12 the next range and so on 10 times.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
Untested, but try
Code:
Sub Testing()
   Dim Wkb As Excel.Workbook
   Dim ws As Sheets
   Dim ws_count As Long
   Dim i As Long
   Dim j As Integer
   Dim k As Integer
   
   Set Wkb = ThisWorkbook
   Set ws = ThisWorkbook.Worksheets
   ws_count = Wkb.Worksheets.Count
   j = 6
   'Begin loop
   For i = 1 To 10
      
      Call RandNumberGen
      Call SimulatedPath
      
      Sheet6.Calculate
      Sheet7.Calculate
      Sheet8.Calculate
      Sheet9.Calculate
      Sheet10.Calculate
      
      With Sheet11
         .Range("A1:C12").Copy
         .Cells(1, j).Resize(12, 3).PasteSpecial xlPasteValues
      End With
      j = j + 5
   Next i
   
   Sheet11.Calculate

End Sub
 

Sherifa

New Member
Joined
Oct 23, 2017
Messages
45
I just tested the code.

Untested, but try
Code:
Sub Testing()
   Dim Wkb As Excel.Workbook
   Dim ws As Sheets
   Dim ws_count As Long
   Dim i As Long
   Dim j As Integer
   Dim k As Integer
   
   Set Wkb = ThisWorkbook
   Set ws = ThisWorkbook.Worksheets
   ws_count = Wkb.Worksheets.Count
   j = 6
   'Begin loop
   For i = 1 To 10
      
      Call RandNumberGen
      Call SimulatedPath
      
      Sheet6.Calculate
      Sheet7.Calculate
      Sheet8.Calculate
      Sheet9.Calculate
      Sheet10.Calculate
      
      With Sheet11
         .Range("A1:C12").Copy
         .Cells(1, j).Resize(12, 3).PasteSpecial xlPasteValues
      End With
      j = j + 5
   Next i
   
   Sheet11.Calculate

End Sub


Thank you, it is pasting in the correct place now, but not doesn't appear to be pasting each time after it runs the following


Call RandNumberGen
Call SimulatedPath

Sheet6.Calculate
Sheet7.Calculate
Sheet8.Calculate
Sheet9.Calculate
Sheet10.Calculate


I want the code to run these steps then paste, run the steps, then paste the next etc.
I'm not sure what is wrong with the code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
It maybe that your sheets arn't being fully calculated, before the paste happens.
However as I rarely use much in the way of formulae, I cannot be sure of that & don't how how to get round it.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,597
Messages
5,597,097
Members
414,123
Latest member
fxledi

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
Top