Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Loop until finished
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default Loop until finished
I need this to loop until it gets all the copy and pasting done.
Sub FormatPage4()
Application.ScreenUpdating = False
em = Range("A2").Value
em = Range("A450").Value
em = Range("A898").Value
em = Range("A1346").Value
em = Range("A1794").Value


Sheets("export_perf_stats_by_center").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("export_perf_stats_by_center").Select
Range("A1").Select

Columns("B:D").EntireColumn.Hidden = True
Columns("G:H").EntireColumn.Hidden = True
Columns("K:S").EntireColumn.Hidden = True
Columns("AA:AG").EntireColumn.Hidden = True
Selection.AutoFilter Field:=5, Criteria1:=em
Range("A2:Z449").Select
Selection.Copy


Sheets("Sheet1").Select
Range("A4").Select --------A15,,,so one down the line
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Application.CutCopyMode = False



Sheets("export_perf_stats_by_center").Select
Selection.AutoFilter Field:=5
Rows("1:2").EntireRow.Hidden = False
Columns("B:D").EntireColumn.Hidden = False
Columns("G:H").EntireColumn.Hidden = False
Columns("K:S").EntireColumn.Hidden = False
Columns("AA:AG").EntireColumn.Hidden = False
Range("A1").Select
Sheets("Sheet1").Select
Range("A4:A30").Select
Selection.Cut
Range("C4").Select
ActiveSheet.Paste
Range("B4:B30").Select
Selection.ClearContents
Range("C4:M30").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
HideRowsWithZeroAHT
Range("A3:C3").Select
ActiveCell.FormulaR1C1 = "=MAX(R[1]C[2]:R[27]C[2])"
Range("B4").Select

Range("A1").Select
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,090
Default Loop until finished
David
Your code, as written, doesn't make any sense. Please post back and
explain what you want to do. Provide as much detail as you can. HTH Otto
"David A." wrote in message
...
I need this to loop until it gets all the copy and pasting done.
Sub FormatPage4()
Application.ScreenUpdating = False
em = Range("A2").Value
em = Range("A450").Value
em = Range("A898").Value
em = Range("A1346").Value
em = Range("A1794").Value


Sheets("export_perf_stats_by_center").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("export_perf_stats_by_center").Select
Range("A1").Select

Columns("B:D").EntireColumn.Hidden = True
Columns("G:H").EntireColumn.Hidden = True
Columns("K:S").EntireColumn.Hidden = True
Columns("AA:AG").EntireColumn.Hidden = True
Selection.AutoFilter Field:=5, Criteria1:=em
Range("A2:Z449").Select
Selection.Copy


Sheets("Sheet1").Select
Range("A4").Select --------A15,,,so one down the line
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Application.CutCopyMode = False



Sheets("export_perf_stats_by_center").Select
Selection.AutoFilter Field:=5
Rows("1:2").EntireRow.Hidden = False
Columns("B:D").EntireColumn.Hidden = False
Columns("G:H").EntireColumn.Hidden = False
Columns("K:S").EntireColumn.Hidden = False
Columns("AA:AG").EntireColumn.Hidden = False
Range("A1").Select
Sheets("Sheet1").Select
Range("A4:A30").Select
Selection.Cut
Range("C4").Select
ActiveSheet.Paste
Range("B4:B30").Select
Selection.ClearContents
Range("C4:M30").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
HideRowsWithZeroAHT
Range("A3:C3").Select
ActiveCell.FormulaR1C1 = "=MAX(R[1]C[2]:R[27]C[2])"
Range("B4").Select

Range("A1").Select
End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default Loop until finished
Ok,
Its very confuing....

I am filtering for names that are found on page "Sheet1" on
"export_perf_stats_by_center" page, then coping that filtered information to
back to "Sheet1" and I need to do this with 15-20 names.

A2: first name
A450:second name
and so on.



"Otto Moehrbach" wrote:

David
Your code, as written, doesn't make any sense. Please post back and
explain what you want to do. Provide as much detail as you can. HTH Otto
"David A." wrote in message
...
I need this to loop until it gets all the copy and pasting done.
Sub FormatPage4()
Application.ScreenUpdating = False
em = Range("A2").Value
em = Range("A450").Value
em = Range("A898").Value
em = Range("A1346").Value
em = Range("A1794").Value


Sheets("export_perf_stats_by_center").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("export_perf_stats_by_center").Select
Range("A1").Select

Columns("B:D").EntireColumn.Hidden = True
Columns("G:H").EntireColumn.Hidden = True
Columns("K:S").EntireColumn.Hidden = True
Columns("AA:AG").EntireColumn.Hidden = True
Selection.AutoFilter Field:=5, Criteria1:=em
Range("A2:Z449").Select
Selection.Copy


Sheets("Sheet1").Select
Range("A4").Select --------A15,,,so one down the line
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Application.CutCopyMode = False



Sheets("export_perf_stats_by_center").Select
Selection.AutoFilter Field:=5
Rows("1:2").EntireRow.Hidden = False
Columns("B:D").EntireColumn.Hidden = False
Columns("G:H").EntireColumn.Hidden = False
Columns("K:S").EntireColumn.Hidden = False
Columns("AA:AG").EntireColumn.Hidden = False
Range("A1").Select
Sheets("Sheet1").Select
Range("A4:A30").Select
Selection.Cut
Range("C4").Select
ActiveSheet.Paste
Range("B4:B30").Select
Selection.ClearContents
Range("C4:M30").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
HideRowsWithZeroAHT
Range("A3:C3").Select
ActiveCell.FormulaR1C1 = "=MAX(R[1]C[2]:R[27]C[2])"
Range("B4").Select

Range("A1").Select
End Sub



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,090
Default Loop until finished
David
I know that you understand perfectly what you are saying, but you are
the only one that does. You must understand that you are talking to people
who know nothing about what you are doing, what you want, or how you want to
do it.
You say:
A2: first name
A450:second name
and so on.
What's in all the other cells?
One way of explaining what you want is to give us a step-by-step explanation
of how YOU would do this if you had to do it manually. HTH Otto
"David A." wrote in message
...
Ok,
Its very confuing....

I am filtering for names that are found on page "Sheet1" on
"export_perf_stats_by_center" page, then coping that filtered information
to
back to "Sheet1" and I need to do this with 15-20 names.

A2: first name
A450:second name
and so on.



"Otto Moehrbach" wrote:

David
Your code, as written, doesn't make any sense. Please post back and
explain what you want to do. Provide as much detail as you can. HTH
Otto
"David A." wrote in message
...
I need this to loop until it gets all the copy and pasting done.
Sub FormatPage4()
Application.ScreenUpdating = False
em = Range("A2").Value
em = Range("A450").Value
em = Range("A898").Value
em = Range("A1346").Value
em = Range("A1794").Value


Sheets("export_perf_stats_by_center").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("export_perf_stats_by_center").Select
Range("A1").Select

Columns("B:D").EntireColumn.Hidden = True
Columns("G:H").EntireColumn.Hidden = True
Columns("K:S").EntireColumn.Hidden = True
Columns("AA:AG").EntireColumn.Hidden = True
Selection.AutoFilter Field:=5, Criteria1:=em
Range("A2:Z449").Select
Selection.Copy


Sheets("Sheet1").Select
Range("A4").Select --------A15,,,so one down the line
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Application.CutCopyMode = False



Sheets("export_perf_stats_by_center").Select
Selection.AutoFilter Field:=5
Rows("1:2").EntireRow.Hidden = False
Columns("B:D").EntireColumn.Hidden = False
Columns("G:H").EntireColumn.Hidden = False
Columns("K:S").EntireColumn.Hidden = False
Columns("AA:AG").EntireColumn.Hidden = False
Range("A1").Select
Sheets("Sheet1").Select
Range("A4:A30").Select
Selection.Cut
Range("C4").Select
ActiveSheet.Paste
Range("B4:B30").Select
Selection.ClearContents
Range("C4:M30").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
HideRowsWithZeroAHT
Range("A3:C3").Select
ActiveCell.FormulaR1C1 = "=MAX(R[1]C[2]:R[27]C[2])"
Range("B4").Select

Range("A1").Select
End Sub





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default Loop until finished
Sorry,
I have a list of names on Sheet1 in column A (In cells: A1,A7,A14......)
I have data on Sheet2 that I need to filter for that name
(A1-"Tom",A7-"Dick",A14-"Harry".....).
Once Sheet2 is filtered for that name(A1-"Tom") it copies that filtered data.
That data is then pasted to Sheet1 (Location: B2). Its aboout 2-3 lines of
data.
I need it to continue to filter and copy for each the names in column A and
paste it to a given location.(B2,B8,B15....) on Sheet1.
Once it gets to the end of the list of names I need it to stop.

"Otto Moehrbach" wrote:

David
I know that you understand perfectly what you are saying, but you are
the only one that does. You must understand that you are talking to people
who know nothing about what you are doing, what you want, or how you want to
do it.
You say:
A2: first name
A450:second name
and so on.
What's in all the other cells?
One way of explaining what you want is to give us a step-by-step explanation
of how YOU would do this if you had to do it manually. HTH Otto
"David A." wrote in message
...
Ok,
Its very confuing....

I am filtering for names that are found on page "Sheet1" on
"export_perf_stats_by_center" page, then coping that filtered information
to
back to "Sheet1" and I need to do this with 15-20 names.

A2: first name
A450:second name
and so on.



"Otto Moehrbach" wrote:

David
Your code, as written, doesn't make any sense. Please post back and
explain what you want to do. Provide as much detail as you can. HTH
Otto
"David A." wrote in message
...
I need this to loop until it gets all the copy and pasting done.
Sub FormatPage4()
Application.ScreenUpdating = False
em = Range("A2").Value
em = Range("A450").Value
em = Range("A898").Value
em = Range("A1346").Value
em = Range("A1794").Value


Sheets("export_perf_stats_by_center").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("export_perf_stats_by_center").Select
Range("A1").Select

Columns("B:D").EntireColumn.Hidden = True
Columns("G:H").EntireColumn.Hidden = True
Columns("K:S").EntireColumn.Hidden = True
Columns("AA:AG").EntireColumn.Hidden = True
Selection.AutoFilter Field:=5, Criteria1:=em
Range("A2:Z449").Select
Selection.Copy


Sheets("Sheet1").Select
Range("A4").Select --------A15,,,so one down the line
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Application.CutCopyMode = False



Sheets("export_perf_stats_by_center").Select
Selection.AutoFilter Field:=5
Rows("1:2").EntireRow.Hidden = False
Columns("B:D").EntireColumn.Hidden = False
Columns("G:H").EntireColumn.Hidden = False
Columns("K:S").EntireColumn.Hidden = False
Columns("AA:AG").EntireColumn.Hidden = False
Range("A1").Select
Sheets("Sheet1").Select
Range("A4:A30").Select
Selection.Cut
Range("C4").Select
ActiveSheet.Paste
Range("B4:B30").Select
Selection.ClearContents
Range("C4:M30").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
HideRowsWithZeroAHT
Range("A3:C3").Select
ActiveCell.FormulaR1C1 = "=MAX(R[1]C[2]:R[27]C[2])"
Range("B4").Select

Range("A1").Select
End Sub






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,090
Default Loop until finished
David
I'm beginning to see the light. Some of it.
You have data in Column A of sheet1 that consists of a name in, say, A1.
Then you have some data below that that goes with the name in A1.
In sheet2, you have much the same thing, the name and then several cells of
data below that. You want those several cells copied from sheet2 and placed
in B2 of sheet1 so that you end up with all the data for that name in
sheet1. Is that right?
Data - Filter will not work with that because there is no way that the
filter can pick up the "other" cells, it sees only the one cell that has the
name.
If I understand you correctly, you will need a looping code to pick up each
name in Column A of sheet1. Looking at this one task by itself, how can the
code distinguish the name from the data in the cells below it? Are the
names always spaced A1, A7, A14, etc? If not, what can the code look for to
find the next name? Maybe a blank cell? How would you find the next name?

Let's look at sheet2. You say it is similar to sheet1 in that the names are
spaced in some pattern. Or is it no pattern?
You want the code to find the name in Column A of sheet2. OK, let's say the
code found it. How can the code figure out how many cells to copy? That's
similar to the problem the code will have with sheet1. How would you figure
out how many cells to copy if you had to do this manually?

It might help if you send me your file or a sample of your file. Fake the
data if you wish. I need just the layout. Don't post it in any newsgroup.
Send it to me, if you wish, directly via email. My email address is
. Remove the "nop" from this address. HTH Otto
"David A." wrote in message
...
Sorry,
I have a list of names on Sheet1 in column A (In cells: A1,A7,A14......)
I have data on Sheet2 that I need to filter for that name
(A1-"Tom",A7-"Dick",A14-"Harry".....).
Once Sheet2 is filtered for that name(A1-"Tom") it copies that filtered
data.
That data is then pasted to Sheet1 (Location: B2). Its aboout 2-3 lines of
data.
I need it to continue to filter and copy for each the names in column A
and
paste it to a given location.(B2,B8,B15....) on Sheet1.
Once it gets to the end of the list of names I need it to stop.

"Otto Moehrbach" wrote:

David
I know that you understand perfectly what you are saying, but you are
the only one that does. You must understand that you are talking to
people
who know nothing about what you are doing, what you want, or how you want
to
do it.
You say:
A2: first name
A450:second name
and so on.
What's in all the other cells?
One way of explaining what you want is to give us a step-by-step
explanation
of how YOU would do this if you had to do it manually. HTH Otto
"David A." wrote in message
...
Ok,
Its very confuing....

I am filtering for names that are found on page "Sheet1" on
"export_perf_stats_by_center" page, then coping that filtered
information
to
back to "Sheet1" and I need to do this with 15-20 names.

A2: first name
A450:second name
and so on.



"Otto Moehrbach" wrote:

David
Your code, as written, doesn't make any sense. Please post back
and
explain what you want to do. Provide as much detail as you can. HTH
Otto
"David A." wrote in message
...
I need this to loop until it gets all the copy and pasting done.
Sub FormatPage4()
Application.ScreenUpdating = False
em = Range("A2").Value
em = Range("A450").Value
em = Range("A898").Value
em = Range("A1346").Value
em = Range("A1794").Value


Sheets("export_perf_stats_by_center").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("export_perf_stats_by_center").Select
Range("A1").Select

Columns("B:D").EntireColumn.Hidden = True
Columns("G:H").EntireColumn.Hidden = True
Columns("K:S").EntireColumn.Hidden = True
Columns("AA:AG").EntireColumn.Hidden = True
Selection.AutoFilter Field:=5, Criteria1:=em
Range("A2:Z449").Select
Selection.Copy


Sheets("Sheet1").Select
Range("A4").Select --------A15,,,so one down the line
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Application.CutCopyMode = False



Sheets("export_perf_stats_by_center").Select
Selection.AutoFilter Field:=5
Rows("1:2").EntireRow.Hidden = False
Columns("B:D").EntireColumn.Hidden = False
Columns("G:H").EntireColumn.Hidden = False
Columns("K:S").EntireColumn.Hidden = False
Columns("AA:AG").EntireColumn.Hidden = False
Range("A1").Select
Sheets("Sheet1").Select
Range("A4:A30").Select
Selection.Cut
Range("C4").Select
ActiveSheet.Paste
Range("B4:B30").Select
Selection.ClearContents
Range("C4:M30").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending,
Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
HideRowsWithZeroAHT
Range("A3:C3").Select
ActiveCell.FormulaR1C1 = "=MAX(R[1]C[2]:R[27]C[2])"
Range("B4").Select

Range("A1").Select
End Sub








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Please Help: Macro Nearly Finished Dave Excel Discussion (Misc queries) 1 September 20th 07 12:16 PM
Loop until finished David A. Excel Discussion (Misc queries) 0 September 17th 07 08:00 PM
Loop until finished. David A. Excel Discussion (Misc queries) 0 September 17th 07 08:00 PM
vba to loop until process finished [email protected] Excel Programming 3 June 15th 07 09:33 AM
Know when .cmd has finished krayten Excel Programming 4 February 14th 06 10:23 PM


All times are GMT +1. The time now is 12:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement