maryam_husn
New Member
- Joined
- Jun 27, 2014
- Messages
- 3
Hello, I have a question that is similar to ones asked before but succinctly different.
i have a macro that
- copies rows from one sheet into another sheet based on a set of criteria
- the very very very first time the macro is run, the first row of data should be put into row 7 (formatting reasons)
- however, every other time it's run it should paste into the next clear row.
but, what is happening is that, each time its run, it puts the data into row 7 => overwriting data.
i've used the offset and counter function but perhaps I need to put it in the header of the code to get it working?
i don't know how to do this.
my code pasted below:
i have a macro that
- copies rows from one sheet into another sheet based on a set of criteria
- the very very very first time the macro is run, the first row of data should be put into row 7 (formatting reasons)
- however, every other time it's run it should paste into the next clear row.
but, what is happening is that, each time its run, it puts the data into row 7 => overwriting data.
i've used the offset and counter function but perhaps I need to put it in the header of the code to get it working?
i don't know how to do this.
my code pasted below:
Code:
Sub originalOPP()
Dim LSearchRow As Integer
Dim LCopyToRowS2 As Integer
Dim LCopyToRowS3 As Integer
Dim LCopyToRowS4 As Integer
Dim LCopyToRowlognew As Integer
Dim LCopyToRowS2NB As Integer
Dim LCopyToRowS2N As Integer
Dim datastatusI2 As String
Dim datastatusA2 As String
Dim ID2 As String
Dim datastatusI3 As String
Dim datastatusA3 As String
Dim ID3 As String
Dim datastatusI4 As String
Dim datastatusA4 As String
Dim ID4 As String
Dim shtname As String
On Error GoTo Err_Execute
'Start search in row 4
LSearchRow = 7
'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRowS2 = 7
LCopyToRowS3 = 7
LCopyToRowS4 = 7
LCopyToRowDL = 2
LCopyToRowlog = 7
While Len(Range("B" & CStr(LSearchRow)).Value) > 0
If Range("D" & CStr(LSearchRow)).Value = "IN PROGRESS" Then
'Select row in Sheet1 to copy
Cells(LSearchRow, 1).Select
shtname = ActiveCell.FormulaR1C1
ID2 = Cells(LSearchRow, 2).Value
datastatusI2 = Cells(LSearchRow, 4).Value
datastatusA2 = Cells(LSearchRow, 5).Value
' Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
'Selection.Copy
'Paste row into Sheet2 in next row
' Sheets("BenefitsTracker").Select
' Rows(CStr(LCopyToRowS2) & ":" & CStr(LCopyToRowS2)).Select
'ActiveSheet.Paste
' copy data to Opp
Sheets("BenefitsTracker").Select
LastrowB = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
LCopyToRowS2N = LastrowB + 1
Cells(LCopyToRowS2N, 2) = ID2
Cells(LCopyToRowS2N, 4) = datastatusI2
Cells(LCopyToRowS2N, 5) = datastatusA2
'Paste row into Log in next row
Sheets("DocumentLog").Select
Lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
LCopyToRowlognew = Lastrow + 1
Sheets("DocumentLog").Select
' Rows(CStr(LCopyToRowlognew) & ":" & CStr(LCopyToRowlognew)).Select
' ActiveSheet.Paste
Sheets("DocumentLog").Select
Cells(LCopyToRowlognew, 2) = ID2
Cells(LCopyToRowlognew, 4) = datastatusI2
Cells(LCopyToRowlognew, 5) = datastatusA2
Cells(LCopyToRowlognew, 6) = Now()
LCopyToRowlognew = LCopyToRowlognew + 1
'Move counter to next row
LCopyToRowS2N = LCopyToRowS2N + 1
' LCopyToRowDL = LCopyToRowDL + 1
LCopyToRowlog = LCopyToRowlog + 1
'Go back to Sheet1 to continue searching
Sheets("OpportunityPrioritisation").Select
End If
If Range("D" & CStr(LSearchRow)).Value = "COMPLETED" Then
'Select row in Sheet1 to copy
Cells(LSearchRow, 1).Select
shtname = ActiveCell.FormulaR1C1
ID2 = Cells(LSearchRow, 2).Value
datastatusI2 = Cells(LSearchRow, 4).Value
datastatusA2 = Cells(LSearchRow, 5).Value
' Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
'Selection.Copy
'Paste row into Sheet2 in next row
' Sheets("BenefitsTracker").Select
' Rows(CStr(LCopyToRowS2) & ":" & CStr(LCopyToRowS2)).Select
'ActiveSheet.Paste
' copy data to Opp
Sheets("BenefitsTracker").Select
LastrowB = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
LCopyToRowS2N = LastrowB + 1
Cells(LCopyToRowS2N, 2) = ID2
Cells(LCopyToRowS2N, 4) = datastatusI2
Cells(LCopyToRowS2N, 5) = datastatusA2
'Paste row into Log in next row
Sheets("DocumentLog").Select
Lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
LCopyToRowlognew = Lastrow + 1
Sheets("DocumentLog").Select
' Rows(CStr(LCopyToRowlognew) & ":" & CStr(LCopyToRowlognew)).Select
' ActiveSheet.Paste
Sheets("DocumentLog").Select
Cells(LCopyToRowlognew, 2) = ID2
Cells(LCopyToRowlognew, 4) = datastatusI2
Cells(LCopyToRowlognew, 5) = datastatusA2
Cells(LCopyToRowlognew, 6) = Now()
LCopyToRowlognew = LCopyToRowlognew + 1
'Move counter to next row
LCopyToRowS2N = LCopyToRowS2N + 1
' LCopyToRowDL = LCopyToRowDL + 1
LCopyToRowlog = LCopyToRowlog + 1
'Go back to Sheet1 to continue searching
Sheets("OpportunityPrioritisation").Select
End If
LSearchRow = LSearchRow + 1
Wend
'Position on cell A3
Application.CutCopyMode = False
' Range("A3").Select
MsgBox "All data has been Transfer."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub
