Excel Copy Rows Based On Boolean?

[Closed]
Report
Posts
2
Registration date
Tuesday March 27, 2012
Status
Member
Last seen
March 28, 2012
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello Everyone,
I've been using these forums for quite some time but I am completely stuck, so this is my first post. I'm currently attempting to Copy rows from sheet "A" to sheet "B" based on a Boolean in column A sheet "A".

Every Row in column "A" that has a TRUE needs to be copied to Sheet "B".
(The Column spans from A6:A28)
Every Row on Sheet "A" to be copied should get data from Range "Data"
(Range "Data" is C6:J28)
They Need To Be Pasted To Sheet "B" into Range "Purchase" from the top down
(Range "Purchase" is B18:I32 on Sheet "B")

I also need a separate macro to clear the data in Range "Purchase" on Sheet "B"
Based on the Boolean "True" in Column "A"


I continue to screw this up and do not know VBA well enough yet. If this has been covered in another post, I apologize. Thank you in advance for any assistance you are able to give me it is much appreciated. Please let me know if I'm not making sense or need to clarify anything I'm trying to do. (Excel 2010 and Windows 7)

Thanks,
VS

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
keep a copy of the file saved somewhere for retrieval if there is some problem

try this macro


Sub test()
Dim r As Range, filt As Range
With Worksheets("sheetB")
.Range("B18:I32").Cells.Clear
End With
With Worksheets("sheetA")
Set r = .Range("A1").CurrentRegion
r.Sort key1:=Range("a1"), order1:=xlDescending, header:=xlYes
r.AutoFilter field:=1, Criteria1:="TRUE"
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible)

filt.Areas(1).Columns("c:J").Copy
With Worksheets("sheetB")
.Range("B18").PasteSpecial
End With
.AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2
Registration date
Tuesday March 27, 2012
Status
Member
Last seen
March 28, 2012

That works great, Thank you. What would I have to add or change to run the macro from Sheet "B"?

Is there any way to not resort the data on Sheet "A" as well?

Again, Thanks.

VS
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
not clear. you park this macro in a module of vb editor and whlichever sheet is active this will run because no sheet is activated.