I have a excel spreadsheet consisting of several tabbed worksheets. I
want to write a macro that will store some information from one
worksheet in another.

For every row in a particular range of worksheet2: if there is something
stored in column 7, then copy the contents of the cells in columns 1 and
3 to the next empty row of worksheet1, storing in columns 1 and 2.

Something like:

repeat for rows from 10 to 200 of worksheet2
if the cell at that row, column 7 of worksheet 2 is not empty then
goto the next empty row of worksheet1 and store some info
store contents of worksheet2: row,1 in worksheet1: next empty row, col1
store contents of worksheet2: row,3 in worksheet1: next empty row,
col2

I hope I have made this understandable. Thanks in advance for any help!

Bob.

Bob


I believe this will give you what you are after


Change sheet names to suit
Sheet 1 must have an entry in it eg headers

change wS2 range to suit


Sub dddd()
Dim c As Range
Dim r As Long
Dim wS2 As Worksheet
Dim wS1 As Worksheet

Set wS1 = Sheets("sheet1")
Set wS2 = Sheets("sheet2")

If wS1.FilterMode Then wS1.ShowAllData
If wS2.FilterMode Then wS2.ShowAllData

For Each c In wS2.Range("g1:g100")
If Not IsEmpty(c) Then

' will error here if nothing exists in wS1
r = wS1.Cells.Find(what:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row + 1

wS1.Cells(r, 1).Value = wS2.Cells(c.Row, 1).Value
wS1.Cells(r, 2).Value = wS2.Cells(c.Row, 3).Value
End If
Next c
End Su

--
Message posted from http://www.ExcelForum.com