How to move down through a list?

Hi,

I have been attempting this but having no luck. I have sheet which I
copy info from A1:A9 to another sheet. Then I save and close that and go
back to the original sheet. I need to move down to row 2 and do all the
copy and paste again. There are about 300 rows and the exact last row
could change. How do I get this to move down to the next row until there
are none left?

Thanks

Gerald
Not sure of what you are asking. What is "this" in:
"How do I get this to move down to the next row until there are none left?"?
Are you using a macro? If so, post back and include the text of your
macro. Don't attach a file, please. HTH Otto

"Gerard Goodland" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have been attempting this but having no luck. I have sheet which I copy
> info from A1:A9 to another sheet. Then I save and close that and go back
> to the original sheet. I need to move down to row 2 and do all the copy
> and paste again. There are about 300 rows and the exact last row could
> change. How do I get this to move down to the next row until there are
> none left?
>
> Thanks
>

Otto,

Below is the full macro. I am copying from the sheet "Contacts" to
sheet " Inventory" . Once the copy is done I save the file using the
value in X4 as the file name and then close the file and go back to thr
original workbook. Now I need to move down to roe 8 and start the whole
process over again untill there are no more rows with entries in the
"Contacts" sheet. From where I select " Contacts" several lines from the
top of the macro to ActiveWorkbook.Close SaveChanges:=True near the
bottom all works ok. It's the part of getting it to move down in the
"Contacts" sheet to the next row I can't get to work. The last row of
the Contacts sheet is not known because it may be added to in the future.

Thanks


With Worksheets("Contacts")
Dim myRow As Long
For myRow = 1 To Range("A65536").End(xlUp).Row

Sheets("Contacts").Select
Range("B7").Select
Selection.Copy
Sheets("Inventory").Select
Range("D4").Select
ActiveSheet.Paste
Sheets("Contacts").Select
Application.CutCopyMode = False
Range("C7").Select
Selection.Copy
Sheets("Inventory").Select
Range("D5").Select
ActiveSheet.Paste
Sheets("Contacts").Select
Application.CutCopyMode = False
Range("D7").Select
Selection.Copy
Sheets("Inventory").Select
Range("D6").Select
ActiveSheet.Paste
Range("D7").Select
Sheets("Contacts").Select
Application.CutCopyMode = False
Range("E7").Select
Selection.Copy
Sheets("Inventory").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Contacts").Select
Range("A7").Select
Selection.Copy
Sheets("Inventory").Select
Range("X4").Select
ActiveSheet.Paste
Sheets("Contacts").Select
Range("F7:G7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Inventory").Select
Range("W6").Select
ActiveSheet.Paste
Sheets("Contacts").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=3
Sheets("Inventory").Select
Range("X4").Select


Sheets("Inventory").Select
Sheets("Inventory").Copy
ActiveWorkbook.SaveAs _
Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
ActiveWorkbook.Close SaveChanges:=True

Next myRow

End With


End Sub


Otto Moehrbach wrote:
> Gerald
> Not sure of what you are asking. What is "this" in:
> "How do I get this to move down to the next row until there are none left?"?
> Are you using a macro? If so, post back and include the text of your
> macro. Don't attach a file, please. HTH Otto
>
> "Gerard Goodland" <[email protected]> wrote in message
> news:[email protected]...
>
>>Hi,
>>
>>I have been attempting this but having no luck. I have sheet which I copy
>>info from A1:A9 to another sheet. Then I save and close that and go back
>>to the original sheet. I need to move down to row 2 and do all the copy
>>and paste again. There are about 300 rows and the exact last row could
>>change. How do I get this to move down to the next row until there are
>>none left?
>>
>>Thanks
>>
>
>
>

Gerald
I'm rewriting your macro for you. Since you are putting the Contact
sheet in the With/End With construct, I'm assuming that the Inventory sheet
is the active sheet. If this is not what you want, contact me direct at
[email protected]. Remove "cobia97" from this address. Otto
"Gerard Goodland" <[email protected]> wrote in message
news:[email protected]...
> Otto,
>
> Below is the full macro. I am copying from the sheet "Contacts" to sheet "
> Inventory" . Once the copy is done I save the file using the value in X4
> as the file name and then close the file and go back to thr original
> workbook. Now I need to move down to roe 8 and start the whole process
> over again untill there are no more rows with entries in the "Contacts"
> sheet. From where I select " Contacts" several lines from the top of the
> macro to ActiveWorkbook.Close SaveChanges:=True near the bottom all works
> ok. It's the part of getting it to move down in the "Contacts" sheet to
> the next row I can't get to work. The last row of the Contacts sheet is
> not known because it may be added to in the future.
>
> Thanks
>
>
> With Worksheets("Contacts")
> Dim myRow As Long
> For myRow = 1 To Range("A65536").End(xlUp).Row
>
> Sheets("Contacts").Select
> Range("B7").Select
> Selection.Copy
> Sheets("Inventory").Select
> Range("D4").Select
> ActiveSheet.Paste
> Sheets("Contacts").Select
> Application.CutCopyMode = False
> Range("C7").Select
> Selection.Copy
> Sheets("Inventory").Select
> Range("D5").Select
> ActiveSheet.Paste
> Sheets("Contacts").Select
> Application.CutCopyMode = False
> Range("D7").Select
> Selection.Copy
> Sheets("Inventory").Select
> Range("D6").Select
> ActiveSheet.Paste
> Range("D7").Select
> Sheets("Contacts").Select
> Application.CutCopyMode = False
> Range("E7").Select
> Selection.Copy
> Sheets("Inventory").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Sheets("Contacts").Select
> Range("A7").Select
> Selection.Copy
> Sheets("Inventory").Select
> Range("X4").Select
> ActiveSheet.Paste
> Sheets("Contacts").Select
> Range("F7:G7").Select
> Application.CutCopyMode = False
> Selection.Copy
> Sheets("Inventory").Select
> Range("W6").Select
> ActiveSheet.Paste
> Sheets("Contacts").Select
> Application.CutCopyMode = False
> ActiveWindow.SmallScroll ToRight:=3
> Sheets("Inventory").Select
> Range("X4").Select
>
>
> Sheets("Inventory").Select
> Sheets("Inventory").Copy
> ActiveWorkbook.SaveAs _
> Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
> ActiveWorkbook.Close SaveChanges:=True
>
> Next myRow
>
> End With
>
>
> End Sub
>
>
> Otto Moehrbach wrote:
>> Gerald
>> Not sure of what you are asking. What is "this" in:
>> "How do I get this to move down to the next row until there are none
>> left?"?
>> Are you using a macro? If so, post back and include the text of your
>> macro. Don't attach a file, please. HTH Otto
>>
>> "Gerard Goodland" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>Hi,
>>>
>>>I have been attempting this but having no luck. I have sheet which I copy
>>>info from A1:A9 to another sheet. Then I save and close that and go back
>>>to the original sheet. I need to move down to row 2 and do all the copy
>>>and paste again. There are about 300 rows and the exact last row could
>>>change. How do I get this to move down to the next row until there are
>>>none left?
>>>
>>>Thanks
>>>
>>
>>
>>
>

Gerald
This macro does what I think you want. Note that the Inventory sheet
must be the active sheet. I changed your scroll code somewhat. Just type
in the row and column of the cell that you want at the top left corner of
the screen. Post back if you need more. HTH Otto
Sub CopyStuff()
Dim myRow As Long
Dim RngOfColB As Range
Dim i As Range
With Worksheets("Contacts")
Set RngOfColB = .Range("B1", .Range("B" & Rows.Count).End(xlUp))
For Each i In RngOfColB
i.Copy Range("D4")
i.Offset(, 1).Copy Range("D5")
i.Offset(, 2).Copy Range("D6")
i.Offset(, 3).Copy Range("D7")
i.Offset(, 4).Copy Range("X4")
i.Offset(, 5).Resize(, 2).Copy Range("W6")
With ActiveWindow
.ScrollRow = 1
.ScrollColumn = 3
End With
ActiveWorkbook.SaveAs _
Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
ActiveWorkbook.Close SaveChanges:=True
Next i
End With
End Sub
"Gerard Goodland" <[email protected]> wrote in message
news:[email protected]...
> Otto,
>
> Below is the full macro. I am copying from the sheet "Contacts" to sheet "
> Inventory" . Once the copy is done I save the file using the value in X4
> as the file name and then close the file and go back to thr original
> workbook. Now I need to move down to roe 8 and start the whole process
> over again untill there are no more rows with entries in the "Contacts"
> sheet. From where I select " Contacts" several lines from the top of the
> macro to ActiveWorkbook.Close SaveChanges:=True near the bottom all works
> ok. It's the part of getting it to move down in the "Contacts" sheet to
> the next row I can't get to work. The last row of the Contacts sheet is
> not known because it may be added to in the future.
>
> Thanks
>
>
> With Worksheets("Contacts")
> Dim myRow As Long
> For myRow = 1 To Range("A65536").End(xlUp).Row
>
> Sheets("Contacts").Select
> Range("B7").Select
> Selection.Copy
> Sheets("Inventory").Select
> Range("D4").Select
> ActiveSheet.Paste
> Sheets("Contacts").Select
> Application.CutCopyMode = False
> Range("C7").Select
> Selection.Copy
> Sheets("Inventory").Select
> Range("D5").Select
> ActiveSheet.Paste
> Sheets("Contacts").Select
> Application.CutCopyMode = False
> Range("D7").Select
> Selection.Copy
> Sheets("Inventory").Select
> Range("D6").Select
> ActiveSheet.Paste
> Range("D7").Select
> Sheets("Contacts").Select
> Application.CutCopyMode = False
> Range("E7").Select
> Selection.Copy
> Sheets("Inventory").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Sheets("Contacts").Select
> Range("A7").Select
> Selection.Copy
> Sheets("Inventory").Select
> Range("X4").Select
> ActiveSheet.Paste
> Sheets("Contacts").Select
> Range("F7:G7").Select
> Application.CutCopyMode = False
> Selection.Copy
> Sheets("Inventory").Select
> Range("W6").Select
> ActiveSheet.Paste
> Sheets("Contacts").Select
> Application.CutCopyMode = False
> ActiveWindow.SmallScroll ToRight:=3
> Sheets("Inventory").Select
> Range("X4").Select
>
>
> Sheets("Inventory").Select
> Sheets("Inventory").Copy
> ActiveWorkbook.SaveAs _
> Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
> ActiveWorkbook.Close SaveChanges:=True
>
> Next myRow
>
> End With
>
>
> End Sub
>
>
> Otto Moehrbach wrote:
>> Gerald
>> Not sure of what you are asking. What is "this" in:
>> "How do I get this to move down to the next row until there are none
>> left?"?
>> Are you using a macro? If so, post back and include the text of your
>> macro. Don't attach a file, please. HTH Otto
>>
>> "Gerard Goodland" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>Hi,
>>>
>>>I have been attempting this but having no luck. I have sheet which I copy
>>>info from A1:A9 to another sheet. Then I save and close that and go back
>>>to the original sheet. I need to move down to row 2 and do all the copy
>>>and paste again. There are about 300 rows and the exact last row could
>>>change. How do I get this to move down to the next row until there are
>>>none left?
>>>
>>>Thanks
>>>
>>
>>
>>
>

Type the row and column number into the macro, not into the cell. HTH Otto
"Otto Moehrbach" <[email protected]> wrote in message
news:[email protected]...
> Gerald
> This macro does what I think you want. Note that the Inventory sheet
> must be the active sheet. I changed your scroll code somewhat. Just type
> in the row and column of the cell that you want at the top left corner of
> the screen. Post back if you need more. HTH Otto
> Sub CopyStuff()
> Dim myRow As Long
> Dim RngOfColB As Range
> Dim i As Range
> With Worksheets("Contacts")
> Set RngOfColB = .Range("B1", .Range("B" & Rows.Count).End(xlUp))
> For Each i In RngOfColB
> i.Copy Range("D4")
> i.Offset(, 1).Copy Range("D5")
> i.Offset(, 2).Copy Range("D6")
> i.Offset(, 3).Copy Range("D7")
> i.Offset(, 4).Copy Range("X4")
> i.Offset(, 5).Resize(, 2).Copy Range("W6")
> With ActiveWindow
> .ScrollRow = 1
> .ScrollColumn = 3
> End With
> ActiveWorkbook.SaveAs _
> Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
> ActiveWorkbook.Close SaveChanges:=True
> Next i
> End With
> End Sub
> "Gerard Goodland" <[email protected]> wrote in message
> news:[email protected]...
>> Otto,
>>
>> Below is the full macro. I am copying from the sheet "Contacts" to sheet
>> " Inventory" . Once the copy is done I save the file using the value in
>> X4 as the file name and then close the file and go back to thr original
>> workbook. Now I need to move down to roe 8 and start the whole process
>> over again untill there are no more rows with entries in the "Contacts"
>> sheet. From where I select " Contacts" several lines from the top of the
>> macro to ActiveWorkbook.Close SaveChanges:=True near the bottom all
>> works ok. It's the part of getting it to move down in the "Contacts"
>> sheet to the next row I can't get to work. The last row of the Contacts
>> sheet is not known because it may be added to in the future.
>>
>> Thanks
>>
>>
>> With Worksheets("Contacts")
>> Dim myRow As Long
>> For myRow = 1 To Range("A65536").End(xlUp).Row
>>
>> Sheets("Contacts").Select
>> Range("B7").Select
>> Selection.Copy
>> Sheets("Inventory").Select
>> Range("D4").Select
>> ActiveSheet.Paste
>> Sheets("Contacts").Select
>> Application.CutCopyMode = False
>> Range("C7").Select
>> Selection.Copy
>> Sheets("Inventory").Select
>> Range("D5").Select
>> ActiveSheet.Paste
>> Sheets("Contacts").Select
>> Application.CutCopyMode = False
>> Range("D7").Select
>> Selection.Copy
>> Sheets("Inventory").Select
>> Range("D6").Select
>> ActiveSheet.Paste
>> Range("D7").Select
>> Sheets("Contacts").Select
>> Application.CutCopyMode = False
>> Range("E7").Select
>> Selection.Copy
>> Sheets("Inventory").Select
>> ActiveSheet.Paste
>> Application.CutCopyMode = False
>> Sheets("Contacts").Select
>> Range("A7").Select
>> Selection.Copy
>> Sheets("Inventory").Select
>> Range("X4").Select
>> ActiveSheet.Paste
>> Sheets("Contacts").Select
>> Range("F7:G7").Select
>> Application.CutCopyMode = False
>> Selection.Copy
>> Sheets("Inventory").Select
>> Range("W6").Select
>> ActiveSheet.Paste
>> Sheets("Contacts").Select
>> Application.CutCopyMode = False
>> ActiveWindow.SmallScroll ToRight:=3
>> Sheets("Inventory").Select
>> Range("X4").Select
>>
>>
>> Sheets("Inventory").Select
>> Sheets("Inventory").Copy
>> ActiveWorkbook.SaveAs _
>> Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
>> ActiveWorkbook.Close SaveChanges:=True
>>
>> Next myRow
>>
>> End With
>>
>>
>> End Sub
>>
>>
>> Otto Moehrbach wrote:
>>> Gerald
>>> Not sure of what you are asking. What is "this" in:
>>> "How do I get this to move down to the next row until there are none
>>> left?"?
>>> Are you using a macro? If so, post back and include the text of
>>> your macro. Don't attach a file, please. HTH Otto
>>>
>>> "Gerard Goodland" <[email protected]> wrote in message
>>> news:[email protected]...
>>>
>>>>Hi,
>>>>
>>>>I have been attempting this but having no luck. I have sheet which I
>>>>copy info from A1:A9 to another sheet. Then I save and close that and go
>>>>back to the original sheet. I need to move down to row 2 and do all the
>>>>copy and paste again. There are about 300 rows and the exact last row
>>>>could change. How do I get this to move down to the next row until there
>>>>are none left?
>>>>
>>>>Thanks
>>>>
>>>
>>>
>>>
>>
>
>

Hi Otto,
Thanks, I will give this a try, but first a couple questions. The info
is copied from " Contacts" sheet to a sheet " Inventory" and I do not
see where that happens in your code. Also what do you mean when you say
to type the row and column number into the macro, where?

Thanks




Otto Moehrbach wrote:
> Type the row and column number into the macro, not into the cell. HTH Otto
> "Otto Moehrbach" <[email protected]> wrote in message
> news:[email protected]...
>
>>Gerald
>> This macro does what I think you want. Note that the Inventory sheet
>>must be the active sheet. I changed your scroll code somewhat. Just type
>>in the row and column of the cell that you want at the top left corner of
>>the screen. Post back if you need more. HTH Otto
>>Sub CopyStuff()
>> Dim myRow As Long
>> Dim RngOfColB As Range
>> Dim i As Range
>> With Worksheets("Contacts")
>> Set RngOfColB = .Range("B1", .Range("B" & Rows.Count).End(xlUp))
>> For Each i In RngOfColB
>> i.Copy Range("D4")
>> i.Offset(, 1).Copy Range("D5")
>> i.Offset(, 2).Copy Range("D6")
>> i.Offset(, 3).Copy Range("D7")
>> i.Offset(, 4).Copy Range("X4")
>> i.Offset(, 5).Resize(, 2).Copy Range("W6")
>> With ActiveWindow
>> .ScrollRow = 1
>> .ScrollColumn = 3
>> End With
>> ActiveWorkbook.SaveAs _
>> Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
>> ActiveWorkbook.Close SaveChanges:=True
>> Next i
>> End With
>>End Sub
>>"Gerard Goodland" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>Otto,
>>>
>>>Below is the full macro. I am copying from the sheet "Contacts" to sheet
>>>" Inventory" . Once the copy is done I save the file using the value in
>>>X4 as the file name and then close the file and go back to thr original
>>>workbook. Now I need to move down to roe 8 and start the whole process
>>>over again untill there are no more rows with entries in the "Contacts"
>>>sheet. From where I select " Contacts" several lines from the top of the
>>>macro to ActiveWorkbook.Close SaveChanges:=True near the bottom all
>>>works ok. It's the part of getting it to move down in the "Contacts"
>>>sheet to the next row I can't get to work. The last row of the Contacts
>>>sheet is not known because it may be added to in the future.
>>>
>>>Thanks
>>>
>>>
>>> With Worksheets("Contacts")
>>> Dim myRow As Long
>>> For myRow = 1 To Range("A65536").End(xlUp).Row
>>>
>>> Sheets("Contacts").Select
>>> Range("B7").Select
>>> Selection.Copy
>>> Sheets("Inventory").Select
>>> Range("D4").Select
>>> ActiveSheet.Paste
>>> Sheets("Contacts").Select
>>> Application.CutCopyMode = False
>>> Range("C7").Select
>>> Selection.Copy
>>> Sheets("Inventory").Select
>>> Range("D5").Select
>>> ActiveSheet.Paste
>>> Sheets("Contacts").Select
>>> Application.CutCopyMode = False
>>> Range("D7").Select
>>> Selection.Copy
>>> Sheets("Inventory").Select
>>> Range("D6").Select
>>> ActiveSheet.Paste
>>> Range("D7").Select
>>> Sheets("Contacts").Select
>>> Application.CutCopyMode = False
>>> Range("E7").Select
>>> Selection.Copy
>>> Sheets("Inventory").Select
>>> ActiveSheet.Paste
>>> Application.CutCopyMode = False
>>> Sheets("Contacts").Select
>>> Range("A7").Select
>>> Selection.Copy
>>> Sheets("Inventory").Select
>>> Range("X4").Select
>>> ActiveSheet.Paste
>>> Sheets("Contacts").Select
>>> Range("F7:G7").Select
>>> Application.CutCopyMode = False
>>> Selection.Copy
>>> Sheets("Inventory").Select
>>> Range("W6").Select
>>> ActiveSheet.Paste
>>> Sheets("Contacts").Select
>>> Application.CutCopyMode = False
>>> ActiveWindow.SmallScroll ToRight:=3
>>> Sheets("Inventory").Select
>>> Range("X4").Select
>>>
>>>
>>>Sheets("Inventory").Select
>>>Sheets("Inventory").Copy
>>>ActiveWorkbook.SaveAs _
>>> Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
>>> ActiveWorkbook.Close SaveChanges:=True
>>>
>>>Next myRow
>>>
>>>End With
>>>
>>>
>>>End Sub
>>>
>>>
>>>Otto Moehrbach wrote:
>>>
>>>>Gerald
>>>> Not sure of what you are asking. What is "this" in:
>>>>"How do I get this to move down to the next row until there are none
>>>>left?"?
>>>> Are you using a macro? If so, post back and include the text of
>>>>your macro. Don't attach a file, please. HTH Otto
>>>>
>>>>"Gerard Goodland" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>
>>>>>Hi,
>>>>>
>>>>>I have been attempting this but having no luck. I have sheet which I
>>>>>copy info from A1:A9 to another sheet. Then I save and close that and go
>>>>>back to the original sheet. I need to move down to row 2 and do all the
>>>>>copy and paste again. There are about 300 rows and the exact last row
>>>>>could change. How do I get this to move down to the next row until there
>>>>>are none left?
>>>>>
>>>>>Thanks
>>>>>
>>>>
>>>>
>>>>
>>
>
>