Copy and Paste cell value based on another cells value

[DK340]

I need a macro that will loops through each value in column (K:K) and if the value, for example K1, is equal to "Credit Card", it needs to find the value in column B of the same row (in this example B1) and copies this value and then pastes this into cell A19. As it goes down through column K and finds more instances of "Credit Card" it pastes the associated data from column B in the A20,A21,A22 etc...until all the data in column B that is associated with "Credit Card" is in column A.

I hope this is clear enough to get some help.

Thanks!

 

[koamapping]

Can I trouble for advice on how to set a variable in a loop that increases the year filtered by one on each repeat of the loop? Rough idea is below. Trying to make "Criteria1" in VBA below increase by one and loop so that each year can be pasted into its own column in a the new sheet ('SantaCruzWY_1941-2013'!).
Thank you for any guidance you can provide.

Sub SeperateWYflows()
'
' SeperateWYflows Macro
' Started at 1947
'

'
ActiveSheet.Range("$L$28:$L$26075").AutoFilter Field:=1, Criteria1:="1947"
Range("D1855").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("SantaCruzWY_1941-2013").Select
Range("I3").Select
ActiveSheet.Paste
Sheets("SantaCruz_1941-2013_11124500").Select
ActiveSheet.Range("$L$28:$L$26075").AutoFilter Field:=1, Criteria1:="1948"
Range("D2220").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SantaCruzWY_1941-2013").Select
Range("J3").Select
ActiveSheet.Paste
Sheets("SantaCruz_1941-2013_11124500").Select
ActiveSheet.Range("$L$28:$L$26075").AutoFilter Field:=1, Criteria1:="1949"
Range("D2586").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SantaCruzWY_1941-2013").Select
Range("K3").Select
ActiveSheet.Paste
Sheets("SantaCruz_1941-2013_11124500").Select
ActiveSheet.Range("$L$28:$L$26075").AutoFilter Field:=1, Criteria1:="1950"
Range("D2951").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SantaCruzWY_1941-2013").Select
Range("L3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=5
Sheets("SantaCruz_1941-2013_11124500").Select
ActiveSheet.Range("$L$28:$L$26075").AutoFilter Field:=1, Criteria1:="1951"
Range("D3316").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SantaCruzWY_1941-2013").Select
Range("M3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

 

[koamapping]

P.S. is there a trick to to hold/add a blank space for leap years on years that don't have a February 29th? Otherwise when pasting into new columns the date column does not align properly.

Appreciate any thoughts

 

[Dkeller12]

This will loop through your range and filter by the starting value you designate as "y", in this case 1947, and loop until Z, your designated end year. I will also paste the data that you are copying into the next column (I3, J3, K3, L3...). It will start at Range D1855 or whatever range you specify in the coding and add 365 to get to the next section, but as you stated in the second part of your question, the leap year becomes an issue. This should get you up to that point.

Sub MacroWY()
y = 1947 'Starting value
Z = 2012 'This can be changed to any maximum value to stop macro
a = 0
b = 0
Do
ActiveSheet.Range("$L$28:$L$26075").AutoFilter Field:=1, Criteria1:=y
Range("D1855").Select
Selection.Offset(b, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("SantaCruzWY_1941-2013").Select
Range("I3").Select
Selection.Offset(0, a).Select
ActiveSheet.Paste
Sheets("SantaCruz_1941-2013_11124500").Select
If y = Z Then Exit Sub
y = y + 1
a = a + 1
b = b + 365
Loop
End Sub

Hope that answered most of your question.

 

[Dkeller12]

This is a little primitive but works. All you need is to know what years are leap years (avaliable on internet) and step through the leap years using a msgbox response. I am sure there is another way but this works.

Sub MacroWY()
Dim Response As VbMsgBoxResult
y = 1947 'Starting value
Z = 2012 'This can be changed to any maximum value to stop macro
a = 0
b = 0
Do
ActiveSheet.Range("$L$28:$L$26075").AutoFilter Field:=1, Criteria1:=y
Range("D1855").Select
Selection.Offset(b, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("SantaCruzWY_1941-2013").Select
Range("I3").Select
Selection.Offset(0, a).Select
ActiveSheet.Paste
Sheets("SantaCruz_1941-2013_11124500").Select
If y = Z Then Exit Sub
'y = y + 1
'a = a + 1
Response = MsgBox("Is " & y & " a leap year?", vbQuestion + vbYesNo)
If Response = vbYes Then GoTo Leap
b = b + 365
GoTo NoLeap
Leap:
b = b + 366
NoLeap:
y = y + 1
a = a + 1
Loop
End Sub

 

[koamapping]

Thank you for your generosity.
I will give it a go and let you know how it works out.