Looping through workbook
 

I have a woksheet that has around 50 lines. In one column I have
downtime for each entry. I am trying to loop though each entry looking
at the downtime and if it is greater than 50 then I want to transfer
the whole line into a special workbook. But here is the catch! I want
to move the previous day lines down as I am adding entries into the
workbook. I am not very good at macros so if you help me please dumb it
down a little. I would appreciate any help at all. Thanks in advance!!!

Looping through workbook
 

Sort descending by the downtime column, select and copy all the lines that
are greater than 50, select your special workbook, right click on a line and
select 'Insert'.

To me that sounds like what you need, and would take more time to code than
to do that simple task, but if you really need it to be coded let me know and
I can list that out in code =).

"bpotter" wrote:

I have a woksheet that has around 50 lines. In one column I have
downtime for each entry. I am trying to loop though each entry looking
at the downtime and if it is greater than 50 then I want to transfer
the whole line into a special workbook. But here is the catch! I want
to move the previous day lines down as I am adding entries into the
workbook. I am not very good at macros so if you help me please dumb it
down a little. I would appreciate any help at all. Thanks in advance!!!

Looping through workbook
 

Sub CopyLines()
Dim wb1 as Workbook
Dim wb2 as Workbook
Dim cnt as Long
Dim lRow as Long 'Last Row

Set wb1 = Workbooks("All.xls") 'Workbook with all down times
Set wb2 = Workbooks("Summary.xls") 'Worbook with 50
lRow = wb1.Sheets("Sheet1").Range("A" & _
wb1.Sheets("Sheet1").Rows.Count).End(xlUp).Row
For cnt = 1 to lRow
If wb1.Sheets("Sheet1").Range("A" & cnt) 50 Then _
'Change "A" to the column in question
wb1.Sheets("Sheet1").Rows(cnt).Copy
wb2.Sheets("Sheet1").Rows(2).Insert Shift:=xlDown 'Assume Header Row
Application.CutCopyMode = False
End If
Next
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"bpotter" wrote:

I have a woksheet that has around 50 lines. In one column I have
downtime for each entry. I am trying to loop though each entry looking
at the downtime and if it is greater than 50 then I want to transfer
the whole line into a special workbook. But here is the catch! I want
to move the previous day lines down as I am adding entries into the
workbook. I am not very good at macros so if you help me please dumb it
down a little. I would appreciate any help at all. Thanks in advance!!!

Looping through workbook
 

Thanks so much for that code. I will try that!!! Also in reply to the
second message. I do this on 15 workbooks for 365 days a year. These
each will have about 50 entries in each.

Looping through workbook
 

If you are using this on multiple workbooks, would you like to have it
cycle through all workbooks in a particular folder and generate one
report for all of them?

Charles

bpotter wrote:
Thanks so much for that code. I will try that!!! Also in reply to the
second message. I do this on 15 workbooks for 365 days a year. These
each will have about 50 entries in each.

Looping through workbook
 

Well CC, I learned something. I never knew a direct insert could be made
from a copy. I have always inserted the row and then did a copy + paste or
copy to. Now I know.

"Die_Another_Day" wrote:

If you are using this on multiple workbooks, would you like to have it
cycle through all workbooks in a particular folder and generate one
report for all of them?

Charles

bpotter wrote:
Thanks so much for that code. I will try that!!! Also in reply to the
second message. I do this on 15 workbooks for 365 days a year. These
each will have about 50 entries in each.

Looping through workbook
 

Just another thing among many that the macro recorder has taught me. <gr

--
Charles Chickering

"Better to remain silent and be thought a fool then to speak out and remove
all doubt." - Abraham Lincoln


"JLGWhiz" wrote:

Well CC, I learned something. I never knew a direct insert could be made
from a copy. I have always inserted the row and then did a copy + paste or
copy to. Now I know.

"Die_Another_Day" wrote:

If you are using this on multiple workbooks, would you like to have it
cycle through all workbooks in a particular folder and generate one
report for all of them?

Charles

bpotter wrote:
Thanks so much for that code. I will try that!!! Also in reply to the
second message. I do this on 15 workbooks for 365 days a year. These
each will have about 50 entries in each.