How to move down through a list?

  • Thread starter Thread starter Gerard Goodland
  • Start date Start date
G

Gerard Goodland

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
 
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
 
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
(e-mail address removed). Remove "cobia97" from this address. Otto
 
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
 
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
 
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
 
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
 
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
(e-mail address removed). Remove "cobia97" from this address. Otto
 
Back
Top