Bernie My New Macro

[Guest]

If you get this I am having trouble Debugging the first four lines.

Sub PrintReports9()
Dim myCell As Range

For Each myCell In Range("BALANCES04").Range("A9"), _
Worksheets("BALANCES04").Range("A65536").End(xlUp))
With Worksheets("STATEMENT")
.Range("C11").Value = myCell.Value
.Range("D24").Value = myCell(1, 2).Value
.Range("J11").Value = myCell(1, 3).Value
.Range("H25").Value = myCell(1, 6).Value
.Range("J32").Value = myCell(1, 13).Value
.Range("J34").Value = myCell(1, 14).Value
.PrintOut
End With
Next myCell

End Sub

The Debug says Syntax Error. The only thing I changed was PrintReports from
2 to a 9 & The third line Range("2") to Range("9")

I don't know what to do.

Julian

 

[Dave Peterson]

I'm not Bernie, but it looks like you may have dropped the worksheets() portion
to that line, too.

For Each myCell In Range(Worksheets("BALANCES04").Range("A9"), _
Worksheets("BALANCES04").Range("A65536").End(xlUp))

I find this method a little easier to read:

Sub PrintReports9A()
Dim myCell As Range
dim myRng as range

with worksheets("balances04")
set myrng = .range("a9",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
With Worksheets("STATEMENT")
.Range("C11").Value = myCell.Value
.Range("D24").Value = myCell(1, 2).Value
.Range("J11").Value = myCell(1, 3).Value
.Range("H25").Value = myCell(1, 6).Value
.Range("J32").Value = myCell(1, 13).Value
.Range("J34").Value = myCell(1, 14).Value
.PrintOut
End With
Next myCell

End Sub