I need to look at information that I just copied over in a macro. How can I refer to the top row again and not to the last row. Here is a small porition of the code.
Dim cellstart As Integer 'starting point of informaiton (row)
Dim commentstart As Integer 'starting point for comments (row)
Dim p As Integer
Dim c As Integer 'for comments
Dim h As Integer 'for comments
Dim x As Integer
Dim r As String 'counting variable for comments
Dim n As String 'counting variable
Dim TestRange As String 'Test String
Dim TestRange2 As String 'Test String
Dim TestRange3 As String 'Test String
Dim TestRange4 As String
Dim TestRange5 As String
Dim TestRange6 As String 'Test String for notes
Dim TestRange7 As String 'Test String for notes
Dim TestRange8 As String 'Test String for notes
Dim TestRange9 As String 'test string for notes
Dim m As String
Dim y As String 'for comments
Dim t As Integer 'for comments
Dim sh As String 'source sheet
Dim destsh As String 'dest sheet
Dim notes As String 'source sheet for comments
Dim reportlocation As String
Dim reportlocation2 As String
Dim header As String 'header you want to run Holds header
Dim output(1 To 8) As String
cellstart = 3 'Starting point of source sheet
commentsstart = 2 'starting point for comments increase this number only if you need more comment space
partstart = 3 'starting point of dest sheet for comments
p = 0
c = 0
header = InputBox("Which header do you want to run?", "header") 'input the requested header
TestRange = header
sh = "Daily" 'Work sheet where the header information is kept
destsh = header ' desination sheet for header
notes = "Notes" 'work sheet for notes
n = cellstart
For x = 1 To 1000 'increase this number only if daily sheet has more than 1000 parts
TestRange2 = "B" + n 'column where to match header request
TestRange3 = "A" + n 'part number
TestRange4 = "AF" + n 'over 60 days
TestRange5 = "AE" + n 'furture 6
If UCase(Trim(TestRange)) = UCase(Trim(sheets(sh).Range(TestRange2))) And UCase(Trim(sheets(sh).Range(TestRange4) < 61)) Then
p = p + 1
m = p + 3 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
TestRange4 = "AF" + n
TestRange5 = "AE" + n
Next
'Move Comments
sheets(destsh).Range("A104:B204").Select
Selection.ClearContents
r = commentsstart
t = partstart
output(1) = "A" + r 'part numbers
output(2) = sheets(notes).Range(output(1)).Value
TestRange8 = output(2)
output(3) = "B" + r 'header
output(4) = sheets(notes).Range(output(3)).Value
output(5) = "C" + r 'comments
output(6) = sheets(notes).Range(output(5)).Text
TestRange7 = output(6)
'------------------ TestRange6 = sheets(destsh).Range(reportlocation) need to get this to read column A row 4 on dest sheet
'------------------ TestRange6 = h + t
For x = 1 To 4000
If UCase(Trim(TestRange)) = UCase(Trim(sheets(notes).Range(output(1)).Value)) And UCase(Trim(sheets(notes).Range(output(5)).Text)) <> "" Then
c = c + 1
y = c + 103 'Tells where to start importing comments
reportlocation = "A" + y 'Starting row of comments
sheets(destsh).Range(reportlocation) = TestRange8
reportlocation = "B" + y
sheets(destsh).Range(reportlocation) = TestRange7
r = r + 1
output(3) = "B" + r
output(5) = "C" + r 'Testrang7
output(1) = "A" + r 'TestRange8
Else
r = r + 1
output(3) = "B" + r
output(5) = "C" + r
output(1) = "A" + r
End If
Next
End Sub
Dim cellstart As Integer 'starting point of informaiton (row)
Dim commentstart As Integer 'starting point for comments (row)
Dim p As Integer
Dim c As Integer 'for comments
Dim h As Integer 'for comments
Dim x As Integer
Dim r As String 'counting variable for comments
Dim n As String 'counting variable
Dim TestRange As String 'Test String
Dim TestRange2 As String 'Test String
Dim TestRange3 As String 'Test String
Dim TestRange4 As String
Dim TestRange5 As String
Dim TestRange6 As String 'Test String for notes
Dim TestRange7 As String 'Test String for notes
Dim TestRange8 As String 'Test String for notes
Dim TestRange9 As String 'test string for notes
Dim m As String
Dim y As String 'for comments
Dim t As Integer 'for comments
Dim sh As String 'source sheet
Dim destsh As String 'dest sheet
Dim notes As String 'source sheet for comments
Dim reportlocation As String
Dim reportlocation2 As String
Dim header As String 'header you want to run Holds header
Dim output(1 To 8) As String
cellstart = 3 'Starting point of source sheet
commentsstart = 2 'starting point for comments increase this number only if you need more comment space
partstart = 3 'starting point of dest sheet for comments
p = 0
c = 0
header = InputBox("Which header do you want to run?", "header") 'input the requested header
TestRange = header
sh = "Daily" 'Work sheet where the header information is kept
destsh = header ' desination sheet for header
notes = "Notes" 'work sheet for notes
n = cellstart
For x = 1 To 1000 'increase this number only if daily sheet has more than 1000 parts
TestRange2 = "B" + n 'column where to match header request
TestRange3 = "A" + n 'part number
TestRange4 = "AF" + n 'over 60 days
TestRange5 = "AE" + n 'furture 6
If UCase(Trim(TestRange)) = UCase(Trim(sheets(sh).Range(TestRange2))) And UCase(Trim(sheets(sh).Range(TestRange4) < 61)) Then
p = p + 1
m = p + 3 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
TestRange4 = "AF" + n
TestRange5 = "AE" + n
Next
'Move Comments
sheets(destsh).Range("A104:B204").Select
Selection.ClearContents
r = commentsstart
t = partstart
output(1) = "A" + r 'part numbers
output(2) = sheets(notes).Range(output(1)).Value
TestRange8 = output(2)
output(3) = "B" + r 'header
output(4) = sheets(notes).Range(output(3)).Value
output(5) = "C" + r 'comments
output(6) = sheets(notes).Range(output(5)).Text
TestRange7 = output(6)
'------------------ TestRange6 = sheets(destsh).Range(reportlocation) need to get this to read column A row 4 on dest sheet
'------------------ TestRange6 = h + t
For x = 1 To 4000
If UCase(Trim(TestRange)) = UCase(Trim(sheets(notes).Range(output(1)).Value)) And UCase(Trim(sheets(notes).Range(output(5)).Text)) <> "" Then
c = c + 1
y = c + 103 'Tells where to start importing comments
reportlocation = "A" + y 'Starting row of comments
sheets(destsh).Range(reportlocation) = TestRange8
reportlocation = "B" + y
sheets(destsh).Range(reportlocation) = TestRange7
r = r + 1
output(3) = "B" + r
output(5) = "C" + r 'Testrang7
output(1) = "A" + r 'TestRange8
Else
r = r + 1
output(3) = "B" + r
output(5) = "C" + r
output(1) = "A" + r
End If
Next
End Sub