So I have this code below that seperately works perfectly fine together however theres a slight problem that I need help fixing.
Code Below:
Sub mickeyb()
Dim EndOf1 As Integer
Dim EndOf2 As Integer
Sheets("dataload").Select
EndOf1 = Sheets("dataload").UsedRange.Rows.Count
EndOf2 = Sheets("rawdata").UsedRange.Rows.Count + 1
Sheets("dataload").Select
Range("A2:D" & EndOf1).Select
Selection.Copy
Sheets("rawdata").Activate
If Range("A2") = "" Then
Range("A2").Select
Else: Range("A" & EndOf2).Select
End If
ActiveSheet.Paste
Dim dbTemp As Integer
Dim s1 As Integer
Dim s2 As Integer
Dim nRow As Long
Dim strTemp1 As String
Dim strTemp2 As String
Dim strTemp3 As String
Dim strTemp4 As String
Sheets("dataload").Select
Range("A1").Select
Sheets("datamanip").Select
Range("C1").Select
sRowPos = 2
A120_Loop:
If Cells(sRowPos, 3) = "" Then
GoTo A120_Exit
End If
Cells(sRowPos, 3).Select
sRowPos = sRowPos + 1
GoTo A120_Loop
A120_Exit:
Sheets("dataload").Select
Range("A1").Select
s1 = 2
A140_Loop:
Sheets("dataload").Select
If Cells(s1, 1) = "" Then
GoTo A140_exit
End If
Cells(s1, 1).Select
strTemp1 = Cells(s1, 1)
strTemp2 = Cells(s1, 2)
strTemp3 = Cells(s1, 3)
strTemp4 = Cells(s1, 4)
Cells(s1, 1) = ""
Cells(s1, 2) = ""
Cells(s1, 3) = ""
Cells(s1, 4) = ""
Sheets("datamanip").Select
Cells(sRowPos, 3).Select
Cells(sRowPos, 3) = strTemp1
Cells(sRowPos, 4) = strTemp2
Cells(sRowPos, 5) = strTemp3
Cells(sRowPos, 6) = strTemp4
s1 = s1 + 1
sRowPos = sRowPos + 1
GoTo A140_Loop
A140_exit:
Sheets("datamanip").Select
For nRow = 2 To Cells(Rows.Count, "C").End(xlUp).Row
If Not IsEmpty(Cells(nRow, "C")) _
And Not IsEmpty(Cells(nRow, "D")) _
And Not IsEmpty(Cells(nRow, "E")) _
And Not IsEmpty(Cells(nRow, "F")) Then
Cells(nRow, "A") = Date
End If
Next nRow
End Sub
The problem is this part of the code instead of the code
Dim EndOf1 As Integer
Dim EndOf2 As Integer
Sheets("dataload").Select
EndOf1 = Sheets("dataload").UsedRange.Rows.Count
EndOf2 = Sheets("rawdata").UsedRange.Rows.Count + 1
Sheets("dataload").Select
Range("A2:D" & EndOf1).Select
Selection.Copy
Sheets("rawdata").Activate
If Range("A2") = "" Then
Range("A2").Select
Else: Range("A" & EndOf2).Select
End If
ActiveSheet.Paste
Instead of the code pasting the data directly under the old data in the next empty cell for some reason it is pasting the data in row 19 missing a whole load of empty cells. I can't really see whats wrong with the code, because in its own macro it works perfect. so please have a look guys
Bookmarks