Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assistance needed with Macro Modification | Excel Programming | |||
Ron De Bruin Code modification needed when naming copied worksheet | Excel Programming | |||
Macro Compiles Sheets to One Book...Small Modification Needed | Excel Programming | |||
need help with macro modification please | Excel Programming | |||
Formula Modification Needed | Excel Programming |