VBA error

[Nurzhan]

Hi All. Pls help me. What's wrong in the following code? The line in red in returns error. But why? Both files i keep open during running macro.
------------------------------------
Sub GL_DPR_FillIn()


' Fills in Report with up-to-date data.


Dim wbA As Workbook
Dim wbB As Workbook
Dim wsName, lastRow
Dim j


Set wbA = Workbooks("GL Rates Calculation.xlsm") 'This one should already be open
Set wbB = Workbooks("DPR_ALS.xlsx")


For j = 5 To 18 Step 1

wbA.Sheets("GL").Range("AG" & "j" & ":" & "AN" & "j").Copy






If j = 5 Then wsName = "Ch22"
If j = 6 Then wsName = "Ch24"
If j = 7 Then wsName = "Ch30"
If j = 8 Then wsName = "Ch54"
If j = 9 Then wsName = "Ch56"
If j = 10 Then wsName = "Ch60"
If j = 11 Then wsName = "Ch62"
If j = 12 Then wsName = "Ch65"
If j = 13 Then wsName = "Ch67"
If j = 14 Then wsName = "Ch115b"
If j = 15 Then wsName = "Ch117"
If j = 16 Then wsName = "Ch123"
If j = 17 Then wsName = "Ch51"
If j = 18 Then wsName = "Ch124"




lastRow = Sheets(wsName).Cells(Rows.Count, "C").End(xlUp).Row
Sheets(wsName).Range("C" & lastRow + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Application.CutCopyMode = False


Next j


End Sub

 

[mrhstn]

Re: VBA error help

Variables shouldn't be enclosed in double quotes

wbA.Sheets("GL").Range("AG" & j & ":" & "AN" & j).Copy

 

[Fluff]

Re: VBA error help

You need to remove the " from the j

[COLOR=#ff0000]wbA.Sheets("GL").Range("AG" & j & ":AN" & j).Copy[/COLOR]

 

[Nurzhan]

Re: VBA error help

Sorry. Those quotes I put trying to check different scenarious to check if that works. But error still appear even without those quotes.

 

[Nurzhan]

Re: VBA error help

now it looks like this but error continues:

Sub GL_DPR_FillIn()


' Fills in Report with up-to-date data.


Dim wbA As Workbook
Dim wbB As Workbook
Dim wsName, lastRow
Dim j


Set wbA = Workbooks("GL Rates Calculation.xlsm") 'This one should already be open
Set wbB = Workbooks("DPR_ALS.xlsx")


For j = 5 To 18 Step 1
'Change "Sheet1" in the following line to your sheet name
wbA.Sheets("GL").Range("AG" & j & ":" & "AN" & j).Copy


If j = 5 Then wsName = "Ch22"
If j = 6 Then wsName = "Ch24"
If j = 7 Then wsName = "Ch30"
If j = 8 Then wsName = "Ch54"
If j = 9 Then wsName = "Ch56"
If j = 10 Then wsName = "Ch60"
If j = 11 Then wsName = "Ch62"
If j = 12 Then wsName = "Ch65"
If j = 13 Then wsName = "Ch67"
If j = 14 Then wsName = "Ch115b"
If j = 15 Then wsName = "Ch117"
If j = 16 Then wsName = "Ch123"
If j = 17 Then wsName = "Ch51"
If j = 18 Then wsName = "Ch124"


lastRow = Sheets(wsName).Cells(Rows.Count, "C").End(xlUp).Row
Sheets(wsName).Range("C" & lastRow + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Application.CutCopyMode = False


Next j
End Sub

 

[Fluff]

Re: VBA error help

Do you have a sheet called GL in workbook GL Rates Calculation.xlsm?
Check for leading/trailing spaces

 

[mrhstn]

Re: VBA error help

As you are pasting as values you can do away with copy and just set the destination as values. Also all the ifs can be condensed, though I'm sure there is a better method than I've gone for

Sub GL_DPR_FillIn()
' Fills in Report with up-to-date data.


Dim wbA As Workbook
Dim wbB As Workbook
Dim lastRow As Integer
Dim j As Integer
Dim dest As Variant
Dim wsName As String


dest = Array("22", "24", "30", "54", "56", "60", "62", "65", "67", "115b", "117", "123", "51", "124")


Set wbA = Workbooks("GL Rates Calculation.xlsm") 'This one should already be open
Set wbB = Workbooks("DPR_ALS.xlsx")




For j = 5 To 18 Step 1


wsName = "Ch" & dest(j - 5)


lastRow = Sheets(wsName).Cells(Rows.Count, "C").End(xlUp).Row + 1
Sheets(wsName).Range("C" & lastRow).Resize(1, 8).Value = wbA.Sheets("GL").Range("AG" & j).Resize(1, 8).Value


Next j




End Sub

 

[Nurzhan]

Re: VBA error help

The problem with this line was a gap in sheet's name.
wbA.Sheets("GL").Range("AG" & j & ":" & "AN" & j).Copy

But now another debugging at:
lastRow = Sheets(wsName).Cells(Rows.Count, "C").End(xlUp).Row

Everything runs good for the first 5 sheets, 22 - 56, but stops at 60 with debugging at lastRow. It's with the original coding.
As for mrhstn's coding it stops right away at lastRow.

 

[mrhstn]

Re: VBA error help

Check for lead and trailing space/characters on all your sheet names.

It would also help if you told us which error you get, saying debug is very limiting given Excel gives you an error code and description.

 

[Nurzhan]

Re: VBA error help

Thank you mrhstn! Now it works perfectly. Found another space in sheet name. Your code works faster.
It's not a big deal but to have this macro work correctly destination workbook "wbB" should be active(both workbooks are open), otherwise the macro doesn't run and debugs at "lastRow". What changes can be made to have it work correctly while any of worbooks are active?
Sorry for silly mistakes, I'm a beginner...