VBA help - worksheets and range questions
 

I am working with a workbook with 3 worksheets named "Sheet1", "wtf",
and "Sheet3." My procedure should look for data in the second sheet,
"wtf", and if found, insert a row on Sheet1 and copy the data.

One problem is that I can't seem to figure out a way to refer to the
sheet by name. A second problem is that I don't want to work with a
fixed number of rows - I would like to determine how many rows have
data in the first column and use that as a loop limit.

Any help would be appreciated. Bob.



The code I have been playing with:

Option Explicit

Sub MoveData()

Dim r As Long
Dim wtf As Worksheet
wtf = ActiveWorkbook.Sheets("wtf")

For r = 1 To 500
If (Sheets("wtf").Cells(r, 3) < "") Or (Sheet2.Cells(r, 4) < "")
Then
Sheet1.Rows(1).Insert
Sheet1.Cells(1, 1) = Sheet2.Cells(r, 3)
Sheet1.Cells(1, 2) = Sheet2.Cells(r, 4)
Sheet1.Cells(1, 3) = Sheet2.Cells(r, 3) + ", " + Sheet2.Cells(r,
4)
End If
Next



End Sub

VBA help - worksheets and range questions
 

Sub MoveData()

Dim r As Long
Dim wtf As Worksheet
set wtf = ActiveWorkbook.Sheets("wtf")

For r = 1 To Cells(rows.count,1).End(xlup).Row
If sh.Cells(r, 3) < "" Or Sheet2.Cells(r, 4) < "" Then
Sheet1.Rows(1).Insert
Sheet1.Cells(1, 1) = Sheet2.Cells(r, 3)
Sheet1.Cells(1, 2) = Sheet2.Cells(r, 4)
Sheet1.Cells(1, 3) = Sheet2.Cells(r, 3) + ", " + Sheet2.Cells(r,
4)
End If
Next

--
Regards,
Tom Ogilvy


End Sub
"Bob" wrote in message
m...
I am working with a workbook with 3 worksheets named "Sheet1", "wtf",
and "Sheet3." My procedure should look for data in the second sheet,
"wtf", and if found, insert a row on Sheet1 and copy the data.

One problem is that I can't seem to figure out a way to refer to the
sheet by name. A second problem is that I don't want to work with a
fixed number of rows - I would like to determine how many rows have
data in the first column and use that as a loop limit.

Any help would be appreciated. Bob.



The code I have been playing with:

Option Explicit

Sub MoveData()

Dim r As Long
Dim wtf As Worksheet
wtf = ActiveWorkbook.Sheets("wtf")

For r = 1 To 500
If (Sheets("wtf").Cells(r, 3) < "") Or (Sheet2.Cells(r, 4) < "")
Then
Sheet1.Rows(1).Insert
Sheet1.Cells(1, 1) = Sheet2.Cells(r, 3)
Sheet1.Cells(1, 2) = Sheet2.Cells(r, 4)
Sheet1.Cells(1, 3) = Sheet2.Cells(r, 3) + ", " + Sheet2.Cells(r,
4)
End If
Next



End Sub

VBA help - worksheets and range questions
 

Your structure **AS IS** of course
Causes your data to be copied to Sheet1 "inverted" (up-side-down).
Is that what you want?

"Bob" wrote in message
m...
I am working with a workbook with 3 worksheets named "Sheet1", "wtf",
and "Sheet3." My procedure should look for data in the second sheet,
"wtf", and if found, insert a row on Sheet1 and copy the data.

One problem is that I can't seem to figure out a way to refer to the
sheet by name. A second problem is that I don't want to work with a
fixed number of rows - I would like to determine how many rows have
data in the first column and use that as a loop limit.

Any help would be appreciated. Bob.



The code I have been playing with:

Option Explicit

Sub MoveData()

Dim r As Long
Dim wtf As Worksheet
wtf = ActiveWorkbook.Sheets("wtf")

For r = 1 To 500
If (Sheets("wtf").Cells(r, 3) < "") Or (Sheet2.Cells(r, 4) < "")
Then
Sheet1.Rows(1).Insert
Sheet1.Cells(1, 1) = Sheet2.Cells(r, 3)
Sheet1.Cells(1, 2) = Sheet2.Cells(r, 4)
Sheet1.Cells(1, 3) = Sheet2.Cells(r, 3) + ", " + Sheet2.Cells(r,
4)
End If
Next



End Sub

VBA help - worksheets and range questions
 

Yes, that is the way the existing spreadsheet is (manually) created.

Thanks for you interest!

JMay wrote:
Your structure **AS IS** of course
Causes your data to be copied to Sheet1 "inverted" (up-side-down).
Is that what you want?

"Bob" wrote in message
m...

I am working with a workbook with 3 worksheets named "Sheet1", "wtf",
and "Sheet3." My procedure should look for data in the second sheet,
"wtf", and if found, insert a row on Sheet1 and copy the data.

One problem is that I can't seem to figure out a way to refer to the
sheet by name. A second problem is that I don't want to work with a
fixed number of rows - I would like to determine how many rows have
data in the first column and use that as a loop limit.

Any help would be appreciated. Bob.



The code I have been playing with:

Option Explicit

Sub MoveData()

Dim r As Long
Dim wtf As Worksheet
wtf = ActiveWorkbook.Sheets("wtf")

For r = 1 To 500
If (Sheets("wtf").Cells(r, 3) < "") Or (Sheet2.Cells(r, 4) < "")
Then
Sheet1.Rows(1).Insert
Sheet1.Cells(1, 1) = Sheet2.Cells(r, 3)
Sheet1.Cells(1, 2) = Sheet2.Cells(r, 4)
Sheet1.Cells(1, 3) = Sheet2.Cells(r, 3) + ", " + Sheet2.Cells(r,
4)
End If
Next



End Sub

VBA help - worksheets and range questions
 

Tom,

I will give this a try when I get into work tomorow. Thanks for the
help. I see your name quite often on the newsgroup. Without people
like you - it wouldn't work for the rest of us. I hope I can give a
little back someday.

Bob.

Tom Ogilvy wrote:
Sub MoveData()

Dim r As Long
Dim wtf As Worksheet
set wtf = ActiveWorkbook.Sheets("wtf")

For r = 1 To Cells(rows.count,1).End(xlup).Row
If sh.Cells(r, 3) < "" Or Sheet2.Cells(r, 4) < "" Then
Sheet1.Rows(1).Insert
Sheet1.Cells(1, 1) = Sheet2.Cells(r, 3)
Sheet1.Cells(1, 2) = Sheet2.Cells(r, 4)
Sheet1.Cells(1, 3) = Sheet2.Cells(r, 3) + ", " + Sheet2.Cells(r,
4)
End If
Next