I am a total amatuer when it comes to VBA and I cannot seem to get this to work. I have 2 columns on Sheet1. I want to loop through column A and find all cells with a value of 1. The I want to copy the cell to the right into a list on Sheet2 without blank spaces.
Sheet 1 Sheet2
A B F
1 Apples Apples
1 Oranges Oranges
0 Peaches Bananas
1 Bananas Grapes
0 Pears
1 Grapes
Thank you,
Bill
her you go
Sub CpyPst()
Dim s1 As Worksheet
Dim s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim i As Long, lr As Long, lr2 As Long
lr = s1.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr
lr2 = s2.Range("F" & Rows.Count).End(xlUp).Row
If s1.Range("A" & i) = 1 Then
s1.Range("B" & i).Copy s2.Range("F" & lr2 + 1)
End If
Next i
End SubThank you. This works great!
One last question. How can I modify this code to start the copied list on a specific row?
Does your data on sheet1 have a Header row?
Another way is using excels built in features:
[VBA]Sub Macro1()
Dim lRowStart As Long
Application.ScreenUpdating = False
With Sheet1
.Rows(lRowStart).AutoFilter Field:=1, Criteria1:="1"
.Rows(lRowStart).Columns(2).Offset(1).Resize(.AutoFilter.Range.Rows.Count).Copy Sheet2.Range("A1")
.UsedRange.AutoFilter
End With
Application.ScreenUpdating = True
End Sub[/VBA]
Changing the x to the starting row number is not working. Regardless of what I change x to, the list always start on row 2. I thought the sample I gave would suffice but maybe not.
The table in on a sheet that has other information on it as well as several other tables.
The table I am referring to actually starts on in column A, row 22 and does have a header. Sorry for the confusion.
Thanks
Should work. Suggest you upload a sample of the actual worksheet sanitized for confidential data Without seeing what you are actually working with, cannot determine causation.
Ok so try:
[VBA]
Sub Macro1()
Dim lRowStart As Long
Application.ScreenUpdating = False
lRowStart = 22
With Sheet1
.Rows(lRowStart).AutoFilter Field:=1, Criteria1:="1"
.Rows(lRowStart).Columns(2).Offset(1).Resize(.AutoFilter.Range.Rows.Count).Copy Sheet2.Range("A1")
.UsedRange.AutoFilter
End With
Application.ScreenUpdating = True
End Sub [/VBA]
I have attached the sanitized worksheet.
Once again, thanks for the assistance.
Using your workbook, I used the following code and it delivered the results as expected in Column F of the Sheet2
Sub ListProds()
Dim s1 As Worksheet
Dim s2 As Worksheet
Set s1 = Sheets("Variables")
Set s2 = Sheets("Sheet2")
Dim i As Long, lr As Long, lr2 As Long
lr = s1.Range("A" & Rows.Count).End(xlUp).Row
For i = 22 To lr
lr2 = s2.Range("F" & Rows.Count).End(xlUp).Row
If s1.Range("A" & i) = 1 Then
s1.Range("B" & i).Copy s2.Range("F" & lr2 + 1)
End If
Next i
End SubData Range
[Table="class: grid"]
F
1
[COLOR="#000000"]
[/COLOR]
[/td][/tr]2
[COLOR="#000000"]
Peaches
[/COLOR]
[/td][/tr]3
[COLOR="#000000"]
Lemons
[/COLOR]
[/td][/tr]4
[COLOR="#000000"]
Pears
[/COLOR]
[/td][/tr]5
[COLOR="#000000"]
Bananas
[/COLOR]
[/td][/tr]6
[COLOR="#000000"]
Plums
[/COLOR]
[/td][/tr]7
[COLOR="#000000"]
Oranges
[/COLOR]
[/td][/tr]8
[COLOR="#000000"]
Tangerines
[/COLOR]
[/td][/tr]9
[COLOR="#000000"]
Other
[/COLOR]
[/td][/tr]
[/table]
Try this
Display MoreUsing your workbook, I used the following code and it delivered the results as expected in Column F of the Sheet2
CodeDisplay MoreSub ListProds() Dim s1 As Worksheet Dim s2 As Worksheet Set s1 = Sheets("Variables") Set s2 = Sheets("Sheet2") Dim i As Long, lr As Long, lr2 As Long lr = s1.Range("A" & Rows.Count).End(xlUp).Row For i = 22 To lr lr2 = s2.Range("F" & Rows.Count).End(xlUp).Row If s1.Range("A" & i) = 1 Then s1.Range("B" & i).Copy s2.Range("F" & lr2 + 1) End If Next i End Sub
Data Range
[tr][td][/td][td]
[Table="class: grid"][/td][/tr]F
[tr][td]
[/td][td]1
[COLOR="#000000"]
[/COLOR]
[/td][/tr][tr][td]
[/td][td]2
[COLOR="#000000"]
Peaches
[/COLOR]
[/td][/tr][tr][td]
[/td][td]3
[COLOR="#000000"]
Lemons
[/COLOR]
[/td][/tr][tr][td]
[/td][td]4
[COLOR="#000000"]
Pears
[/COLOR]
[/td][/tr][tr][td]
[/td][td]5
[COLOR="#000000"]
Bananas
[/COLOR]
[/td][/tr][tr][td]
[/td][td]6
[COLOR="#000000"]
Plums
[/COLOR]
[/td][/tr][tr][td]
[/td][td]7
[COLOR="#000000"]
Oranges
[/COLOR]
[/td][/tr][tr][td]
[/td][td]8
[COLOR="#000000"]
Tangerines
[/COLOR]
[/td][/tr][tr][td]
[/td][td]9
[COLOR="#000000"]
Other
[/COLOR]
[/td][/tr]
[/table]
This code does work. My questions is, how do I modify the code to start the list on row 8 instead of row 2 in column F.
Don’t have an account yet? Register yourself now and be a part of our community!