VBA newb needing help with an IF
 

I am trying to create something that will look a a range of columns sa
B2 to G2 andsee if i have written ture in them

There will only be one cell that will be marked true at once

If i do i want to copy that column from range 2-13 or something t
another workbook

I have recorded a macro for the copying and I have found a for loop
can modify to look at each row for true. The If.Then statment is givin
me a really hard time. If anyone can help point me in the writ
direction I would be very greatful. Thanks

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

VBA newb needing help with an IF
 

Assume us column B to determine the extent of your data

Assume destination is Book2.xls, first worksheet.

Sub CopyTrueRows()

Dim rng as Range, cell as Range
set rng = Range(Cells(1,2),Cells(row.count,2).end(xlup))

for each cell in rng
if cell.offset(0,-1) = True then
cell.EntireRow.copy Destination:= _
workbooks("Book2.xls").Worksheets(1) _
.Cells(rows.count,1).end(xlup)
end if
Next

End Sub
--
regards,
Tom Ogilv

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

VBA newb needing help with an IF
 

Assume us column B to determine the extent of your data

Assume destination is Book2.xls, first worksheet.

Sub CopyTrueRows()
Dim cell1 as Range, bCopy as boolean
Dim rng as Range, cell as Range
set rng = Range(Cells(1,2),Cells(row.count,2).end(xlup))

for each cell in rng
bCopy = false
for each cell1 in cell.Resize(1,6)
if cell1 = True then
bCopy = True
exit for
end if
Next
if bCopy then
cell.EntireRow.copy Destination:= _
workbooks("Book2.xls").Worksheets(1) _
.Cells(rows.count,1).end(xlup)
end if
Next

End Sub


--
regards,
Tom Ogilv

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

VBA newb needing help with an IF
 

I'm sure that someone will send you code to do this in one step. But
here is a non-elegant, straightforward way to do what you want:

Sub Macro1()

For j = 2 To 7 'j is the column number
If Cells(2, j) = "True" Then
Range(Cells(2, j), Cells(13, j)).Select
Selection.Copy
Workbooks("the other workbook").Activate
Sheets("wherever it is you want to paste").Activate
Range("wherever it is you want to paste").Select
ActiveSheet.Paste
End If
Next j

End Su

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

VBA newb needing help with an IF
 

whoops, try this instead. I am not sure what range 2 - 13 means

Assume us column B to determine the extent of your data

Assume destination is Book2.xls, first worksheet.

Sub CopyTrueRows()
Dim cell1 as Range, bCopy as boolean
Dim rng as Range, cell as Range
set rng = Range(Cells(1,2),Cells(row.count,2).end(xlup))

for each cell in rng
bCopy = false
for each cell1 in cell.Resize(1,6)
if cell1 = True then
bCopy = True
exit for
end if
Next
if bCopy then
cell.EntireRow.copy Destination:= _
workbooks("Book2.xls").Worksheets(1) _
..Cells(rows.count,1).end(xlup)
end if
Next

End Sub


If it means columns 2 to 13

Sub CopyTrueRows()
Dim cell1 as Range, bCopy as boolean
Dim rng as Range, cell as Range
set rng = Range(Cells(1,2),Cells(row.count,2).end(xlup))

for each cell in rng
bCopy = false
for each cell1 in cell.Resize(1,6)
if cell1 = True then
bCopy = True
exit for
end if
Next
if bCopy then
cell.Resize(1,12).copy Destination:= _
workbooks("Book2.xls").Worksheets(1) _
..Cells(rows.count,1).end(xlup)
end if
Next

End Sub



--
regards,
Tom Ogilvy


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