VBA - Copy/Paste with variable cell range - 1004 error

[FawnCC]

I am getting a Run-time error '1004':Application-defined or object-defined error in copy/paste section of my code and I can't figure out why it is doing that. The last thing I tried was to add Cstr to the ranges.

This macro is pulling information from one sheet and putting it to another, the sheet I'm pulling it from needs to be updated weekly but the I also need static numbers for comparison purposes.

Any help would be appreciated.




Sub Mid_Month_Creation()

Dim ColNo As Long, NextCol As Long, PLColNo As Long, PLColNo1 As Long
Dim ColFind As Range, PLColFind As Range
Dim EoMonth As Variant, valPLCM As Variant, valYTG As Variant
Dim lRow As Integer, x As Integer
Dim ProfitCode As String

'Finding the first column that contains the date specified by the user
Sheets("Mid-Month").Range("A2:CYY2").NumberFormat = "M/D/YYYY"
EoMonth = InputBox("Input the last date of the month for current Mid-Month Forecast." & vbNewLine & "Please use the M/D/YYYY format.")
Set ColFind = Sheets("Mid-Month").Range("A2:CYY2").Find(what:=DateValue(EoMonth), LookIn:=xlFormulas)
ColNo = ColFind.Column
Sheets("Mid-Month").Range(Cells(1, ColNo), Cells(125, 972)).ClearContents <---This works


'Finds the Column in the P&L Tab that corresponds to the date specified by the user
Set PLColFind = Sheets("P&L").Range("BU3:CF3").Find(what:=DateValue(EoMonth), LookIn:=xlFormulas)
PLColNo = PLColFind.Column
PLColNo1 = PLColFind.Column + 1


'Loop 1
lRow = Sheets("Temp").Range("A" & Rows.Count).End(xlUp).Row
x = 1
Do
x = x + 1
ProfitCode = Sheets("Temp").Range("A" & x).Value

Sheets("P&L").Range("A3").Value = ProfitCode
Sheets("P&L").Calculate

If Sheets("Mid-Month").Range("A1").Value = "" Then
NextCol = 1
Else
NextCol = Sheets("Mid-Month").Cells(1, Columns.Count).End(xlToLeft).Column + 1
End If

Sheets("P&L").Range(Cells(2, CStr(PLColNo)), Cells(126, CStr(PLColNo))).Copy <--Run-time error '1004':Application-defined or object-defined error
Sheets("Mid-Month").Cells(1, CStr(NextCol)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Loop Until x = lRow

End Sub

 

[steve the fish]

You need to qualify the sheet on the cells part too. Sheets("P&L").Cells etc.

 

[steve the fish]

And you should do that on the other one you said works also. It did because that sheet was the activesheet.

 

[FawnCC]

Thank you! That worked perfectly!

 

[HPotter]

You need to qualify the sheet on the cells part too. Sheets("P&L").Cells etc.

what do you mean by qualify the sheet? Can you comment the code itself? I have same problem as OP and I couldn't figure out yet.