VBA Reference a Dynamic Range within R1C1

[XfortunaX]

Hello,

I am using Excel 2010 and am stuck on inputting the last row within a RefersToR1C1 range. I am getting the Run-time error '1004': The formula you types contains an error.

The code is to find the last used row in column A, take that number and input it into the R1C1 range listed below in red. Currently, Lrow works in finding the last used row, I just cannot get the number into the line of code in red below.

The code:

Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)

Dim plage As Object
Dim Commission As Range
Dim Lrow As Long
Dim coversheet As Workbook, master As Workbook, WB As Workbook

'Name the workbooks that are open
Set master = ActiveWorkbook
For Each WB In Application.Workbooks
If WB.Name Like "Cover Sheet Commission Breakout*" Then WB.Activate
Next WB
Set coversheet = ActiveWorkbook


'Find the last used row
Lrow = ActiveSheet.UsedRange.Rows.Count

'Create the JPG
Worksheets(1).Activate
ActiveWorkbook.Names.Add Name:="Commission", RefersToR1C1:= _
"=CoverSheet!R[1]C[1]:R[Lrow]C[4]"
Set plage = Worksheets(1).Range("Commission")
plage.CopyPicture
With Worksheets(1).ChartObjects.Add(plage.Left, plage.Top, plage.Width, plage.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
End With
Worksheets(1).ChartObjects(Worksheets(1).ChartObjects.Count).Delete
Set plage = Nothing
End Sub

Thank you for any help you can offer,

Tuna

 

[Marcelo Branco]

Do you want to create a range CoverSheet!$A$1:$D$x where x = Lrow?

If so try

ActiveWorkbook.Names.Add Name:="Commission", RefersToR1C1:= _
"=CoverSheet!R1C1:R" & Lrow & "C4"

Observe that i used R1C1 not R[1]C[1], i.e., i used absolute references (Row 1, Column 1)

Hope this helps

M.

 

[XfortunaX]

Marcelo Branco,

Thank you! Yes, that worked perfectly.

Have a good one.