Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping a column in workbook 1 to workbook 2's sheets | Excel Programming | |||
Workbook looping to many worksheets | Excel Programming | |||
Looping worksheets in workbook | Excel Programming | |||
Looping thru spreadsheets in a workbook | Excel Programming | |||
Looping Through Worksheets In A Workbook | Excel Programming |