Run time error 1004

[Sherifa]

PasteSpecial METHOD OF RANGE CLASS FAILED

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

 

[Fluff]

You haven;t fully specified the range, try

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

 

[Sherifa]

You haven;t fully specified the range, try

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.

 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]

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]

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]

What are you trying to do with that portion of the code?

 

[Sherifa]

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]

Untested, but try

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]

I just tested the code.

Untested, but try

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]

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.