Nucor
10-30-2009, 08:34 AM
Here is my coding...this is my first post, so i'm assuming that it's ok to post code here...if not, SORRY !!!
Sub Macro2()
'
' This searches using the GL as the constant
'
Dim X As String
Sheets("Reports").Select
X = Range("H7").Value
Sheets("Input List").Select
Range("L11").Select
Cells.Find(What:=X, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Activate
ActiveCell.Offset(0, -8).Activate 'QSR NUMBER
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE QSR NUMBER
Range("B18").Select
empty_cell 'run empty cell routine
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 3).Activate 'TREND
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE TREND
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 1).Activate 'OCCURENCE
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE OCCURENCE
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 5).Activate 'COST
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE COST
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 1).Activate 'DESCRIPTION
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE DESCRIPTION
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Do
Do While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
If ActiveCell.Text = "" Then
ActiveCell.Activate
End If
Loop
Loop Until ActiveCell.Text = ""
End Sub
The loop is where i'm getting stuck. What i need the code to do, is search through a sheet (Input List is the name of the sheet), look for the text that is in a certain cell ( H7 of the Input List sheet), copy a certain range of cells, and then pastes it on the second sheet ( Reports is the name of the sheet), (here comes the problem) :banghead: go back to the first sheet, start looking at the information again, copy and pasting ONLY the next row of cells that has the same value in H& from the input list sheet. this process will run till it hits the last row for input (row 1162) OR to make it faster, till there is a blank cell with no value in it.
Can ANYONE please help me ?!?!?!?!
Thanks in advance,
Nucor
Sub Macro2()
'
' This searches using the GL as the constant
'
Dim X As String
Sheets("Reports").Select
X = Range("H7").Value
Sheets("Input List").Select
Range("L11").Select
Cells.Find(What:=X, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Activate
ActiveCell.Offset(0, -8).Activate 'QSR NUMBER
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE QSR NUMBER
Range("B18").Select
empty_cell 'run empty cell routine
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 3).Activate 'TREND
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE TREND
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 1).Activate 'OCCURENCE
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE OCCURENCE
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 5).Activate 'COST
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE COST
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input List").Select
ActiveCell.Offset(0, 1).Activate 'DESCRIPTION
Selection.Copy
Sheets("Reports").Select 'COPY/PASTE DESCRIPTION
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Do
Do While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
If ActiveCell.Text = "" Then
ActiveCell.Activate
End If
Loop
Loop Until ActiveCell.Text = ""
End Sub
The loop is where i'm getting stuck. What i need the code to do, is search through a sheet (Input List is the name of the sheet), look for the text that is in a certain cell ( H7 of the Input List sheet), copy a certain range of cells, and then pastes it on the second sheet ( Reports is the name of the sheet), (here comes the problem) :banghead: go back to the first sheet, start looking at the information again, copy and pasting ONLY the next row of cells that has the same value in H& from the input list sheet. this process will run till it hits the last row for input (row 1162) OR to make it faster, till there is a blank cell with no value in it.
Can ANYONE please help me ?!?!?!?!
Thanks in advance,
Nucor