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

outlawdevil

Board Regular
Joined
Jun 30, 2009
Messages
238
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!
 

Attachments

  • mirror copy.jpg
    mirror copy.jpg
    253.5 KB · Views: 10
I am so sorry, I just tested it out. It is doing what’s intended to do.
Phew! I was beginning to think I was going crazy there for a while. :biggrin:


Do you mind explaining how that was achieved Using With statement and offset?

Using this as a simple example
outlawdevil.xlsm
ABCD
1NameaddrDueDept
2Name 1Addr 15HR
3Name 2Addr 28HR
4Name 3Addr 33HR
5Name 4Addr 42HR
6
7
8
9
Sheet1


VBA Code:
With Range("A2", Range("D" & Rows.Count).End(xlUp))
This sets the original range by starting at A2 and then coming up column D from the bottom of the sheet until it meets data. So the original range is set to A2:D5

VBA Code:
.Copy Destination:=.Offset(.Rows.Count)
Using the range set above (A2:D5) copy it and paste to the destination that is the original range offset (downwards) by the number of rows in that original range (4). A2:D5 offset down by 4 rows is A6:D9

VBA Code:
.Columns(4).Offset(.Rows.Count).Value = "AP"
Column 4 of the original range is D2:D5 so offset that again by 4 rows brings you to D6:D9 & put "AP" in those cells.

Hope that makes sense. :)
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Phew! I was beginning to think I was going crazy there for a while. :biggrin:




Using this as a simple example
outlawdevil.xlsm
ABCD
1NameaddrDueDept
2Name 1Addr 15HR
3Name 2Addr 28HR
4Name 3Addr 33HR
5Name 4Addr 42HR
6
7
8
9
Sheet1


VBA Code:
With Range("A2", Range("D" & Rows.Count).End(xlUp))
This sets the original range by starting at A2 and then coming up column D from the bottom of the sheet until it meets data. So the original range is set to A2:D5

VBA Code:
.Copy Destination:=.Offset(.Rows.Count)
Using the range set above (A2:D5) copy it and paste to the destination that is the original range offset (downwards) by the number of rows in that original range (4). A2:D5 offset down by 4 rows is A6:D9

VBA Code:
.Columns(4).Offset(.Rows.Count).Value = "AP"
Column 4 of the original range is D2:D5 so offset that again by 4 rows brings you to D6:D9 & put "AP" in those cells.

Hope that makes sense. :)
I know, I was searching everywhere for middle row vba trying to come up with a solution and it can be achieved by With and offset synx! Thanks again!
 
Upvote 0
" .Columns(4).Offset(.Rows.Count).Value = " what should I do if I want this column value to be negative or *-1? Thanks.
 
Upvote 0
I was thinking maybe this will work but it didn't.:confused:


.Columns(4).Offset(.Rows.Count).Value = ABS(.Columns(4).Offset(.Rows.Count).Value)*-1
 
Upvote 0
what should I do if I want this column value to be negative or *-1?
This column (col 4) is text, not numeric, at least in the only sample we have seen so far so multiplying by -1 does not make sense.

What about another small sample and the expected results with XL2BB and a new explanation in relation to that sample?
 
Upvote 0
This column (col 4) is text, not numeric, at least in the only sample we have seen so far so multiplying by -1 does not make sense.

What about another small sample and the expected results with XL2BB and a new explanation in relation to that sample?

sorry, I have changed the column from text to numerics. As you can see, now I want that offset column to have negative value of copied range. thanks. IE.

Book2
ABCD
1NameaddrDeptDue
2Name 1Addr 1HR5
3Name 2Addr 2HR8
4Name 3Addr 3HR3
5Name 4Addr 4HR2
6Name 1Addr 1HR-5
7Name 2Addr 2HR-8
8Name 3Addr 3HR-3
9Name 4Addr 4HR-2
Sheet1
 
Upvote 0
sorry, I have changed the column from text to numerics.
OK, thanks for the new sample & results. Try this version.

VBA Code:
Sub CopyAndChange_v2()
  With Range("A2", Range("D" & Rows.Count).End(xlUp))
    .Copy Destination:=.Offset(.Rows.Count)
    .Columns(4).Offset(.Rows.Count).Value = Evaluate("-" & .Columns(4).Address)
  End With
End Sub
 
Upvote 0
Solution
OK, thanks for the new sample & results. Try this version.

VBA Code:
Sub CopyAndChange_v2()
  With Range("A2", Range("D" & Rows.Count).End(xlUp))
    .Copy Destination:=.Offset(.Rows.Count)
    .Columns(4).Offset(.Rows.Count).Value = Evaluate("-" & .Columns(4).Address)
  End With
End Sub
I just tested it out, this code is perfect. Much appreciated!
 
Upvote 0

Forum statistics

Threads
1,213,582
Messages
6,114,471
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top