Repeat Macros Function/Program

[RobMack]

I have a automated process set up in an excel template. The porcess takes
info from one sheet and puts associated information into another, based on a
cell reference. I need the process to repeat over and over, until all the
cells have been referenced. Can someone tell me how to program this
repeating process/

Thanks

[Jim Thomlinson]

This is very generic code to traverse a range of cells... It looks at Column
A on Sheet 1 and traverses From A1 to A? where ? is the last populated cell...

Sub TraverseCells()
Dim rngCurrent As Range
Dim rngToSearch As Range
Dim wks As Worksheet

Set wks = Sheets("Sheet1")
With wks
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
End With

For Each rngCurrent In rngToSearch
MsgBox rngCurrent.Value
Next rngCurrent
End Sub
--
HTH...

Jim Thomlinson


"RobMack" wrote:

> I have a automated process set up in an excel template. The porcess takes
> info from one sheet and puts associated information into another, based on a
> cell reference. I need the process to repeat over and over, until all the
> cells have been referenced. Can someone tell me how to program this
> repeating process/
>
> Thanks

[RobMack]

I have posted code for process I am working on, and was wondering if you
might suggest how to implement the "repeat" function you described?

Sheets("Sheet1").Select
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Find(What:="na", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Cells.Replace What:="na", Replacement:="jays", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A24").Select
ActiveWindow.Close

Thanks


"RobMack" wrote:

> I have a automated process set up in an excel template. The porcess takes
> info from one sheet and puts associated information into another, based on a
> cell reference. I need the process to repeat over and over, until all the
> cells have been referenced. Can someone tell me how to program this
> repeating process/
>
> Thanks

[Jim Thomlinson]

So is your intention to repeat this code for each cell in column A of sheet 1
and paste that value into B16 of Sheet2. Your do a find (for no reason that I
can tell) and then you do a find and replace. Does the find and replace need
to be done over and over? Then you print. Then you select range A24 for no
reason that I can tell) and then you close the active window. Why do you
close the active window???

My big question is what exactly needs to be repeated?
--
HTH...

Jim Thomlinson


"RobMack" wrote:

> I have posted code for process I am working on, and was wondering if you
> might suggest how to implement the "repeat" function you described?
>
> Sheets("Sheet1").Select
> Range("A2").Select
> Selection.Copy
> Sheets("Sheet2").Select
> Range("B16").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> With Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .WrapText = True
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Cells.Find(What:="na", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
> , SearchFormat:=False).Activate
> Cells.Replace What:="na", Replacement:="jays", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
>
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> Range("A24").Select
> ActiveWindow.Close
>
> Thanks
>
>
> "RobMack" wrote:
>
> > I have a automated process set up in an excel template. The porcess takes
> > info from one sheet and puts associated information into another, based on a
> > cell reference. I need the process to repeat over and over, until all the
> > cells have been referenced. Can someone tell me how to program this
> > repeating process/
> >
> > Thanks

[RobMack]

Yes that is my intention. I would like this process to repeat for all cells
in column "A" that are filled, using multiple reference files(excel
worksheets that are pasted into template before macros is run), all with
different numbers of observations.

The process involves pasting into another worksheet that has vlookup
formulas referenced to the pasted cell. After the cell reference is pasted,
a number of fields automatically fill because of the vlookup. It is then
sent to print.

The need for find and find & replace is as described. I need the vlookup
template to recognize the array name, and I don't want the array name in the
template because I have had many problems with copying pasting formatting.

I wanted to close the "active" sheet so that the vlookup formulas would go
back to original when the sheet was re-opened, and so the find & replace
function would work based upon exisitng template formula references.

Essentially I want to repeat the cut, paste, and print, functions. If you
can suggest any changes or additions, that would help significantly.


"Jim Thomlinson" wrote:

> So is your intention to repeat this code for each cell in column A of sheet 1
> and paste that value into B16 of Sheet2. Your do a find (for no reason that I
> can tell) and then you do a find and replace. Does the find and replace need
> to be done over and over? Then you print. Then you select range A24 for no
> reason that I can tell) and then you close the active window. Why do you
> close the active window???
>
> My big question is what exactly needs to be repeated?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "RobMack" wrote:
>
> > I have posted code for process I am working on, and was wondering if you
> > might suggest how to implement the "repeat" function you described?
> >
> > Sheets("Sheet1").Select
> > Range("A2").Select
> > Selection.Copy
> > Sheets("Sheet2").Select
> > Range("B16").Select
> > ActiveSheet.Paste
> > Application.CutCopyMode = False
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .WrapText = True
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > Cells.Find(What:="na", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
> > xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:=False _
> > , SearchFormat:=False).Activate
> > Cells.Replace What:="na", Replacement:="jays", LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> > ReplaceFormat:=False
> >
> > ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> > Range("A24").Select
> > ActiveWindow.Close
> >
> > Thanks
> >
> >
> > "RobMack" wrote:
> >
> > > I have a automated process set up in an excel template. The porcess takes
> > > info from one sheet and puts associated information into another, based on a
> > > cell reference. I need the process to repeat over and over, until all the
> > > cells have been referenced. Can someone tell me how to program this
> > > repeating process/
> > >
> > > Thanks