Loop until finished

G

Guest

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
 
O

Otto Moehrbach

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
 
G

Guest

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 said:
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. said:
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
Click to expand...
Click to expand...
 
O

Otto Moehrbach

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. said:
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 said:
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. said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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. said:
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 said:
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
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
Click to expand...
Click to expand...
Click to expand...
 
O

Otto Moehrbach

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
(e-mail address removed). Remove the "nop" from this address. HTH Otto
David A. said:
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 said:
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. said:
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.



:

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
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
Trying to hide all rows where no data is in columns AQ-AV 2
Slow Macro 3
Activating a workbook 1
File Name changes with dates and Deleting Rows of Zeros 0
sort macro not working 1
Sum the remaining rows and place the result in the row below the last 6
VBA Pivot Table Error 1

Top