开发者

Why is my VBA excel sheet not copying over?

I am new to this and don't know why my sheet is not copying over the the new worksheet? I can't find the error in my VBA.

Private Sub Adminminreport_Click()

Application.ScreenUpdating = False

Dim i&, LR&, count&

LR = Worksheets("Parts").Range("J" & Rows.count).End(xlUp).Row
Set newWS = Worksheets.Add

Worksheets("Parts").Range(Worksheet开发者_如何学Gos("Parts").Cells(1, 1), Worksheets("Parts").Cells(1, 13)).Copy newWS.Range("A1")
count = 2

For i = 2 To LR
    If Range("J" & i).Value < Range("L" & i).Value Then
        Worksheets("Parts").Range(Worksheets("Parts").Cells(i, 1), Worksheets("Parts").Cells(i, 13)).Copy newWS.Range("A" & count)
        count = count + 1
    End If
Next i

Application.ScreenUpdating = True

Unload Me

newWS.Activate

End Sub


It's good practice to always qualify a Range object with its parent worksheet. Otherwise you're relying on a certain sheet being active when your code runs...

Private Sub Adminminreport_Click()

   Dim i As Long, LR As Long, count As Long
   Dim newWS As Worksheet, partsWS As Worksheet

   Set newWS = Worksheets.Add()
   Set partsWS = Worksheets("Parts")

   Application.ScreenUpdating = False

   LR = partsWS.Range("J" & Rows.count).End(xlUp).Row
   Range(partsWS.Cells(1, 1), partsWS.Cells(1, 13)).Copy _
                                     newWS.Range("A1")
   count = 2

   For i = 2 To LR
    If partsWS.Range("J" & i).Value < partsWS.Range("L" & i).Value Then
         Range(partsWS.Cells(i, 1), partsWS.Cells(i, 13)).Copy _
                                    newWS.Range("A" & count)
         count = count + 1
    End If
   Next i

   Application.ScreenUpdating = True

   newWS.Activate
   Unload Me

End Sub
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜