Philnblanks
New Member
- Joined
- Aug 22, 2019
- Messages
- 4
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
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
Code:
'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
Code:
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