Macro Modification help needed

A

akemeny

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!
 
J

Joel

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Modification 1
Macro Modification help 3
Getvalues Macro Help Needed 2
Paste Special Macro Assistance needed 5
Macro Modification Help 1
Queries 0
vba 1
Modify sub to include sheetname in new col 10

Top