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
ottomocobia97@bellsouth.net. Remove "cobia97" from this address. Otto
"Gerard Goodland" <ggoodland@ns.sympatico.ca> wrote in message
news:IXPGd.213280$Np3.8945741@ursa-nb00s0.nbnet.nb.ca...
> 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" <ggoodland@ns.sympatico.ca> wrote in message
>> news:B2OGd.213230$Np3.8943485@ursa-nb00s0.nbnet.nb.ca...
>>
>>>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
>>>
>>
>>
>>
>