Macro Modification help needed
 

I have tried reworking my macro in different ways, but can't get it to do
what I need. How can I change the macro below so that it will start pasting
the information in Column B instead of Column A?

Private Sub Workbook_Open()
lr = Application.Max(2, Cells(Rows.Count, 2).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Workbooks("QIC.xls").Worksheets("Overturns_QIC")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 2 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "y") = 30 Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "y") = 30 And Not .Cells(i, "x") = "Paid" Then
..Rows(i).Copy Rows(dlr)
Next i
With Workbooks("FI.xls").Worksheets("Overturns_FI")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 2 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "y") = 30 Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "y") = 30 And Not .Cells(i, "x") = "Paid" Then
..Rows(i).Copy Rows(dlr)
Next i
End With
End With
End Sub

Thanks!

Macro Modification help needed
 

Your code is copying Rows. You can't copy and row and then paste it into a
range object. They are different sizes. In excel 2003 you only have 256
columns. If you copy a row of 256 columns and paste starting at column B
only 255 columns are available and you will get an error.

Make this change. Similar change is required in two locations in the code

from
If .Cells(i, "y") = 30 And Not .Cells(i, "x") = "Paid" Then
.Rows(i).Copy Rows(dlr)

to
If .Cells(i, "y") = 30 And Not .Cells(i, "x") = "Paid" Then
LastColumn = .cells(i,columns.count).end(xltoleft).Column
Set CopyRange = .Range(.Range("B" & i),.cells(i,LastColumn))
CopyRange.Copy Destination:=Range("B" & dlr)

"akemeny" wrote:

I have tried reworking my macro in different ways, but can't get it to do
what I need. How can I change the macro below so that it will start pasting
the information in Column B instead of Column A?

Private Sub Workbook_Open()
lr = Application.Max(2, Cells(Rows.Count, 2).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Workbooks("QIC.xls").Worksheets("Overturns_QIC")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 2 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "y") = 30 Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "y") = 30 And Not .Cells(i, "x") = "Paid" Then
.Rows(i).Copy Rows(dlr)
Next i
With Workbooks("FI.xls").Worksheets("Overturns_FI")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 2 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "y") = 30 Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "y") = 30 And Not .Cells(i, "x") = "Paid" Then
.Rows(i).Copy Rows(dlr)
Next i
End With
End With
End Sub

Thanks!