How to move down through a list?

Gerard Goodland · 01-17-2005, 09:06 AM

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 Moehrbach · 01-17-2005, 10:06 AM

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
>

Gerard Goodland · 01-17-2005, 11:06 AM

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
>>
>
>
>

Otto Moehrbach · 01-17-2005, 02:06 PM

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
>>>
>>
>>
>>
>

Otto Moehrbach · 01-17-2005, 03:06 PM

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
>>>
>>
>>
>>
>

Otto Moehrbach · 01-17-2005, 03:06 PM

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
>>>>
>>>
>>>
>>>
>>
>
>

Gerard Goodland · 01-17-2005, 04:06 PM

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
>>>>>
>>>>
>>>>
>>>>
>>
>
>

Otto Moehrbach · 01-17-2005, 07:06 PM

Gerald
Let's look at the macro a piece at a time. Notice that most of the
macro is inside of a With/End With construct with sheet Contacts.
The Set statement sets the total range of Column B from B1 to the last entry
in Column B of the Contacts sheet.
The For/Next statement is a looping procedure that loops through each entry
in Column B.
Think of it as each loop will cover all the cells in one row of the Contacts
sheet.
The statement i.Copy Range("D4") says to copy the Column B entry and paste
it to D4 of the active sheet (Inventory sheet). Note that there must be a
space after the word "Copy".
The statement i.Offset(, 1).Copy Range("D5") says to copy the Column C entry
and paste it to D5 of the active sheet.
And so on.

About the scrolling. Look at the 2 lines:
..ScrollRow = 1
..ScrollColumn = 3
This says for Excel to scroll the active sheet so that cell C1 is in the top
left corner of the active sheet.
If you change those 2 lines to:
..ScrollRow = 5
..ScrollColumn = 6
then cell F5 will be scrolled to the top left corner of the active sheet.
BTW, I use the Resize statement in this procedure. I have Excel 2002. If
you have an earlier version, you may not be able to use this statement. If
that is the case, you will get an error. Click on Debug of the error box
and it will take you to the offending line of code, the Resize line. If
this happens, post back and I will change the code to do the same thing but
without that feature. HTH Otto

"Gerard Goodland" <[email protected]> wrote in message
news:[email protected]...
>
>
> 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
>>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>
>>
>

Gerard Goodland · 01-17-2005, 09:06 PM

Otto,

I appreciate the help. But I am totally lost and not getting anywhere
with it. The code copied below, which is a combination from several
sources, does everything I want it to do but it keeps doing the same row
and not moving down to the next row in " Contacts".If you could find my
mistake I would really appreciate it before I abondon this project
completely.

Thanks

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
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

With ActiveWindow
.ScrollRow = 1
.ScrollColumn = 3
End With

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

Next i
End With

End Sub

Otto Moehrbach wrote:
> Gerald
> Let's look at the macro a piece at a time. Notice that most of the
> macro is inside of a With/End With construct with sheet Contacts.
> The Set statement sets the total range of Column B from B1 to the last entry
> in Column B of the Contacts sheet.
> The For/Next statement is a looping procedure that loops through each entry
> in Column B.
> Think of it as each loop will cover all the cells in one row of the Contacts
> sheet.
> The statement i.Copy Range("D4") says to copy the Column B entry and paste
> it to D4 of the active sheet (Inventory sheet). Note that there must be a
> space after the word "Copy".
> The statement i.Offset(, 1).Copy Range("D5") says to copy the Column C entry
> and paste it to D5 of the active sheet.
> And so on.
>
> About the scrolling. Look at the 2 lines:
> .ScrollRow = 1
> .ScrollColumn = 3
> This says for Excel to scroll the active sheet so that cell C1 is in the top
> left corner of the active sheet.
> If you change those 2 lines to:
> .ScrollRow = 5
> .ScrollColumn = 6
> then cell F5 will be scrolled to the top left corner of the active sheet.
> BTW, I use the Resize statement in this procedure. I have Excel 2002. If
> you have an earlier version, you may not be able to use this statement. If
> that is the case, you will get an error. Click on Debug of the error box
> and it will take you to the offending line of code, the Resize line. If
> this happens, post back and I will change the code to do the same thing but
> without that feature. HTH Otto
>
>
> "Gerard Goodland" <[email protected]> wrote in message
> news:[email protected]...
>
>>
>>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
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>
>
>

Otto Moehrbach · 01-18-2005, 03:06 PM

Gerald
If you wish, send me, direct, your file or a small version of your file.
Include in the file a sample of what you have to start with as well as a
sample of what you want to have after the code runs. My email address is
[email protected]. Remove "cobia97" from this address. Otto
"Gerard Goodland" <[email protected]> wrote in message
news:[email protected]...
>
> Otto,
>
> I appreciate the help. But I am totally lost and not getting anywhere with
> it. The code copied below, which is a combination from several sources,
> does everything I want it to do but it keeps doing the same row and not
> moving down to the next row in " Contacts".If you could find my mistake I
> would really appreciate it before I abondon this project completely.
>
> Thanks
>
>
> 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
> 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
>
> With ActiveWindow
> .ScrollRow = 1
> .ScrollColumn = 3
> End With
>
> Sheets("Inventory").Select
> Sheets("Inventory").Copy
> ActiveWorkbook.SaveAs _
> Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
> ActiveWorkbook.Close SaveChanges:=True
>
>
>
>
>
> Next i
> End With
>
> End Sub
>
> Otto Moehrbach wrote:
>> Gerald
>> Let's look at the macro a piece at a time. Notice that most of the
>> macro is inside of a With/End With construct with sheet Contacts.
>> The Set statement sets the total range of Column B from B1 to the last
>> entry in Column B of the Contacts sheet.
>> The For/Next statement is a looping procedure that loops through each
>> entry in Column B.
>> Think of it as each loop will cover all the cells in one row of the
>> Contacts sheet.
>> The statement i.Copy Range("D4") says to copy the Column B entry and
>> paste it to D4 of the active sheet (Inventory sheet). Note that there
>> must be a space after the word "Copy".
>> The statement i.Offset(, 1).Copy Range("D5") says to copy the Column C
>> entry and paste it to D5 of the active sheet.
>> And so on.
>>
>> About the scrolling. Look at the 2 lines:
>> .ScrollRow = 1
>> .ScrollColumn = 3
>> This says for Excel to scroll the active sheet so that cell C1 is in the
>> top left corner of the active sheet.
>> If you change those 2 lines to:
>> .ScrollRow = 5
>> .ScrollColumn = 6
>> then cell F5 will be scrolled to the top left corner of the active sheet.
>> BTW, I use the Resize statement in this procedure. I have Excel 2002.
>> If you have an earlier version, you may not be able to use this
>> statement. If that is the case, you will get an error. Click on Debug
>> of the error box and it will take you to the offending line of code, the
>> Resize line. If this happens, post back and I will change the code to do
>> the same thing but without that feature. HTH Otto
>>
>>
>> "Gerard Goodland" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>
>>>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
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>
>>
>>
>