Copy cells (values & Formatting) from one worksheet to another.

[Philnblanks]

First off I'm newish to VBA (so please be kind)

I trying to write a Sub that will loop through a number of cells (values and formats) from a "Source" worksheet to a "Target" worksheet. I found some code that copies values and formats (one cell at a time). So I put it into nested For Loops and the Call to this code crashes [Run-time error '9': Subscript out of range]

The first code snippet is the code I found on the web
BTW this code works fine when I call it a single time with this line of code: Call Copy_Format(Range("ONGOING!B2"), Range("BF2"))

The second snippet is the code I wrote that crashes.
I'm sure the it has to do with how I'm addressing the cells in the Call but I can't figure out the correct way.

Any help is appreciated

'Copy Value anf formats from one cell to another
Sub Copy_Format(cell1 As Range, cell2 As Range)
Dim sel As Range
Set sel = Selection

Application.ScreenUpdating = False

cell1.Copy
cell2.PasteSpecial Paste:=xlPasteFormats
cell1.Copy
cell2.PasteSpecial Paste:=xlPasteValues
sel.Activate

Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

Sub TestForNext()
Dim i As Long
Dim j As Long

Sheets("JUNK").Select
Cells.ClearContents

For i = 1 To 10   'Row Counter
    For j = 1 To 20   'Column Counter
    Call Copy_Format(Sheets("ONGOING!").Cells(i, j), Sheets("JUNK").Cells(i, j)) 'CRASHS
    Next j
Next i

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Philnblanks()
   With Sheets("JUNK")
      .Cells.ClearContents
   
       Sheets("ONGOING!").Range("A1:T10").Copy
       .Range("A1").PasteSpecial xlPasteFormats
       .Range("A1").PasteSpecial xlPasteValues
   End With
   Application.CutCopyMode = False
End Sub

 

[Philnblanks]

Fluff

That would work for a single row but I need to copy multiple rows and the number of rows will vary each time I run this report.

 

[Fluff]

It should do exactly the same as your code.

 

[Philnblanks]

Hi Fluff,

I copied your code into a module, modified the Range to match the range I wanted copied then ran it from the module and got the "Subscript out of range" error again.
What am I missing here?

 

[Fluff]

Do you have a sheet called JUNK and another called ONGOING!
Also did you get the same error with your code?