cut and paste with loop macro
 

I am very novice....so I have not found enough "simple" info to solve this
problem....
I need a macro that searches out the rows labelled "Archive" (in Column E)
(in the "Master" sheet), cuts and pastes the whole row to another sheet,
labelled "Archive". But the formula stops after one paste and only searches
out cells below the active row. It also does not clear the control check
boxes that I've got in (every second column) N through AB (8 total). I'm
trying to totally automate this workbook so a macro is preferable.

I do not want the empty rows deleted or it will mess up my control boxes, I
just re-sort them and the empty rows go to the bottom.
Thanks


Cells.Find(What:="Archive", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Cut
Sheets("Archive").Select
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Master").Select

cut and paste with loop macro
 

hi
I think you have left out a WHOLE lot of code. per the code you have
posted.... the reason it only does it once is because you dont have a loop.
and per my experience... cut and paste in a loop will crash your macro due to
memory errors. better to use variables and avoid the clipboard alltogeather.
cut and paste ...once or twice is ok...but not in a loop perticularly is the
data has thousands of lines.
Post ALL of your code so that we can get a better picture of what is
happening. perticularly about the "check boxes" which the code you posted
does not address.

Regards
FSt1

"Nic" wrote:

I am very novice....so I have not found enough "simple" info to solve this
problem....
I need a macro that searches out the rows labelled "Archive" (in Column E)
(in the "Master" sheet), cuts and pastes the whole row to another sheet,
labelled "Archive". But the formula stops after one paste and only searches
out cells below the active row. It also does not clear the control check
boxes that I've got in (every second column) N through AB (8 total). I'm
trying to totally automate this workbook so a macro is preferable.

I do not want the empty rows deleted or it will mess up my control boxes, I
just re-sort them and the empty rows go to the bottom.
Thanks


Cells.Find(What:="Archive", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Cut
Sheets("Archive").Select
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Master").Select

cut and paste with loop macro
 

Hmm, well as a novice, that's about all I have so far, I didn't know it was
missing so much. I'm only going to have about 100 total lines max, because we
will be archiving so much regularly. It will probably only have about 5-8
archived at one time as well.
As for the checkboxes, I'm not sure what code there is, I've used the
control box to create it, and I've linked it to the cell underneath, so I
think I just need to figure out how to change the cells to FALSE to make it
clear the checkmark?
If this is too much work, could you direct me to a learning resource where I
can do more research to understand my errors?
Thanks for your response!!


"FSt1" wrote:

hi
I think you have left out a WHOLE lot of code. per the code you have
posted.... the reason it only does it once is because you dont have a loop.
and per my experience... cut and paste in a loop will crash your macro due to
memory errors. better to use variables and avoid the clipboard alltogeather.
cut and paste ...once or twice is ok...but not in a loop perticularly is the
data has thousands of lines.
Post ALL of your code so that we can get a better picture of what is
happening. perticularly about the "check boxes" which the code you posted
does not address.

Regards
FSt1

"Nic" wrote:

I am very novice....so I have not found enough "simple" info to solve this
problem....
I need a macro that searches out the rows labelled "Archive" (in Column E)
(in the "Master" sheet), cuts and pastes the whole row to another sheet,
labelled "Archive". But the formula stops after one paste and only searches
out cells below the active row. It also does not clear the control check
boxes that I've got in (every second column) N through AB (8 total). I'm
trying to totally automate this workbook so a macro is preferable.

I do not want the empty rows deleted or it will mess up my control boxes, I
just re-sort them and the empty rows go to the bottom.
Thanks


Cells.Find(What:="Archive", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Cut
Sheets("Archive").Select
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Master").Select

cut and paste with loop macro
 

hi again
not too much work. the code you posted is simple enoght. just need some
"cleaning up". one queston.... is column E a solid block of data or are there
gaps(blank cells)?
The "check boxes" remain a problem.

regards
FSt1

"Nic" wrote:

Hmm, well as a novice, that's about all I have so far, I didn't know it was
missing so much. I'm only going to have about 100 total lines max, because we
will be archiving so much regularly. It will probably only have about 5-8
archived at one time as well.
As for the checkboxes, I'm not sure what code there is, I've used the
control box to create it, and I've linked it to the cell underneath, so I
think I just need to figure out how to change the cells to FALSE to make it
clear the checkmark?
If this is too much work, could you direct me to a learning resource where I
can do more research to understand my errors?
Thanks for your response!!


"FSt1" wrote:

hi
I think you have left out a WHOLE lot of code. per the code you have
posted.... the reason it only does it once is because you dont have a loop.
and per my experience... cut and paste in a loop will crash your macro due to
memory errors. better to use variables and avoid the clipboard alltogeather.
cut and paste ...once or twice is ok...but not in a loop perticularly is the
data has thousands of lines.
Post ALL of your code so that we can get a better picture of what is
happening. perticularly about the "check boxes" which the code you posted
does not address.

Regards
FSt1

"Nic" wrote:

I am very novice....so I have not found enough "simple" info to solve this
problem....
I need a macro that searches out the rows labelled "Archive" (in Column E)
(in the "Master" sheet), cuts and pastes the whole row to another sheet,
labelled "Archive". But the formula stops after one paste and only searches
out cells below the active row. It also does not clear the control check
boxes that I've got in (every second column) N through AB (8 total). I'm
trying to totally automate this workbook so a macro is preferable.

I do not want the empty rows deleted or it will mess up my control boxes, I
just re-sort them and the empty rows go to the bottom.
Thanks


Cells.Find(What:="Archive", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Cut
Sheets("Archive").Select
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Master").Select

cut and paste with loop macro
 

hi again,
it's close to midnight here. I'm crashing. I will look for your post
tommorrow.
sorry.
regards
FSt1


"FSt1" wrote:

hi again
not too much work. the code you posted is simple enoght. just need some
"cleaning up". one queston.... is column E a solid block of data or are there
gaps(blank cells)?
The "check boxes" remain a problem.

regards
FSt1

"Nic" wrote:

Hmm, well as a novice, that's about all I have so far, I didn't know it was
missing so much. I'm only going to have about 100 total lines max, because we
will be archiving so much regularly. It will probably only have about 5-8
archived at one time as well.
As for the checkboxes, I'm not sure what code there is, I've used the
control box to create it, and I've linked it to the cell underneath, so I
think I just need to figure out how to change the cells to FALSE to make it
clear the checkmark?
If this is too much work, could you direct me to a learning resource where I
can do more research to understand my errors?
Thanks for your response!!


"FSt1" wrote:

hi
I think you have left out a WHOLE lot of code. per the code you have
posted.... the reason it only does it once is because you dont have a loop.
and per my experience... cut and paste in a loop will crash your macro due to
memory errors. better to use variables and avoid the clipboard alltogeather.
cut and paste ...once or twice is ok...but not in a loop perticularly is the
data has thousands of lines.
Post ALL of your code so that we can get a better picture of what is
happening. perticularly about the "check boxes" which the code you posted
does not address.

Regards
FSt1

"Nic" wrote:

I am very novice....so I have not found enough "simple" info to solve this
problem....
I need a macro that searches out the rows labelled "Archive" (in Column E)
(in the "Master" sheet), cuts and pastes the whole row to another sheet,
labelled "Archive". But the formula stops after one paste and only searches
out cells below the active row. It also does not clear the control check
boxes that I've got in (every second column) N through AB (8 total). I'm
trying to totally automate this workbook so a macro is preferable.

I do not want the empty rows deleted or it will mess up my control boxes, I
just re-sort them and the empty rows go to the bottom.
Thanks


Cells.Find(What:="Archive", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Cut
Sheets("Archive").Select
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Master").Select