I need a solution to mirror copy and filter by mirror copy only. Any suggestions?

[outlawdevil]

Hi, I got a question that i haven't got a solution,maybe someone can help me. I am open for any suggestions. I have a list of data I want to mirror copy(A2:D5) and paste to the last available empty row. I need excel to figure out where the mid point is so I can filter and change from mid point from HR to AP(A6:D9). These range will change based on data so it's not fixed. Is there a solution or code I should look into to accomplish this task? Screenshot attached for reference. thank you in advance!

 

[outlawdevil]

any suggestions on how to let excel identify middle row? thanks.

 

[Peter_SSs]

See if this does what you want. Test with a copy of your data.

BTW you will generally get faster help if you provide your sample data in a form that can easily be copied. See XL2BB

Sub CopyAndChange()
  With Range("A2", Range("D" & Rows.Count).End(xlUp))
    .Copy Destination:=.Offset(.Rows.Count)
    .Columns(4).Offset(.Rows.Count).Value = "AP"
  End With
End Sub

 

[outlawdevil]

See if this does what you want. Test with a copy of your data.

BTW you will generally get faster help if you provide your sample data in a form that can easily be copied. See XL2BB

Sub CopyAndChange()
  With Range("A2", Range("D" & Rows.Count).End(xlUp))
    .Copy Destination:=.Offset(.Rows.Count)
    .Columns(4).Offset(.Rows.Count).Value = "AP"
  End With
End Sub

Hi, thanks for your VBA code. This doesn't take into account that list of data will change will each mirror copy range. I was hoping somewhere it can determine the middle range with each mirror copy.

 

[Peter_SSs]

Hi, thanks for your VBA code. This doesn't take into account that list of data will change will each mirror copy range. I was hoping somewhere it can determine the middle range with each mirror copy.

I don't understand what you mean. Can you spell it out in more detail and/or give specific examples?

 

[outlawdevil]

I don't understand what you mean. Can you spell it out in more detail and/or give specific examples?

Sure, above example is just one dataset. Which imported from a file A2:D5 so My macro is doing a mirror copy of that data to A6:D9 but I wanted excel Macro to be able to filter based on the range that just copied and replace column D from HR to AP. The tricky thing about this that File imported range will be different each time as I try to save this as a template to automate . Let’s say next set of data will be A2:D16. The mirror copy will be In range A16:D32. How would I be able to let excel to just pick that Copied range so I can replace that copied range column with different depts. I hope this clears up. Thanks for your response.

 

[Peter_SSs]

Sorry, still not entirely clear. Once the range has been copied, do you want to

1. manually change the bottom half departments to various different departments?
2. manually change the bottom half departments to a single different department? (AP?)
3. have the code change the bottom half departments to a single different department? (If so, how would the code know what department to change to?)
4. something else?

 

[outlawdevil]

Sorry, still not entirely clear. Once the range has been copied, do you want to

1. manually change the bottom half departments to various different departments?
2. manually change the bottom half departments to a single different department? (AP?)
3. have the code change the bottom half departments to a single different department? (If so, how would the code know what department to change to?)
4. something else?

I just need change to a single department but since data range imported is different each time, how would you build something to select the bottom half range only? So I can record a quick find and replace macro or filter to change dept.

 

[Peter_SSs]

My code does change the bottom half to a single department, no matter what size the original range was. In what way is it not meeting your needs?

 

[outlawdevil]

My code does change the bottom half to a single department, no matter what size the original range was. In what way is it not meeting your needs?

I am so sorry, I just tested it out. It is doing what’s intended to do. Thank you so much! Do you mind explaining how that was achieved Using With statement and offset? I would love to learn that. Thanks.