MACRO to retain a copy of a certain range to different sheet collectively

[archvanarl]

I have uploaded a sample file with 2 sheets on it. The PO and the PO_VAULT.

Here is the thing, I wonder if it is possible to have a macro on this whenever activated, the data on PO sheet cell A1:M180 will be duplicated to PO_VAULT sheet on cell cell A1:M180 (Just like a copy and paste whilst keeping the source column width.
The data on PO sheet changes from one transaction to another so for the 2nd transaction, when the macro is activated, since the first transaction has been copied on PO_VAULT sheet at cell A1:M180 and it covers from column A to M, the next transaction will be copied and pasted (whilst keeping the column width) on cell N1 to X180,
3rd will be on:
columns Y1 to AI180
4th will be on:
Columns AJ1 to AT180 and so on...

Is it possible?

Edited:

Please Login or Register  to view this content.

This code works but could someone add a pop up msg if the value in "PO_VAULT" sheet row 7 has a duplicated Po control number? These PO numbers are located first at cell I7, second is v7, third is AI7, next AV7 and so on (Same interval) with a msg pop up of "Duplicated Purchase order number detected, Delete it first from the vault." and it will cancel the effect or function of this macro

[Mumps1]

Try:

Please Login or Register  to view this content.

[archvanarl]

Thank you it worked!hmm can i ask more? Could you add a pop up msg "Purchase Order has been vaulted"?

[Mumps1]

Try:

Please Login or Register  to view this content.

[archvanarl]

I have one more problem, the data on PO are linked from the other sheets. Is there a way to copy and paste it as values whilst keeping the source column width because in this code copies the formula of that table?

Edit: I found a solution

Sub CopyData()
Application.ScreenUpdating = False
Dim lCol As Long, srcWS As Worksheet, desWS As Worksheet
Set srcWS = Sheets("PO")
Set desWS = Sheets("PO_VAULT")
lCol = desWS.Cells(19, Columns.Count).End(xlToLeft).Column + 1
If lCol = 2 Then lCol = 1
srcWS.Range("A1:M180").Copy
With desWS.Cells(1, lCol)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteFormats
End With
MsgBox "Purchase Order has been vaulted."
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[Mumps1]

Try:

Please Login or Register  to view this content.

[archvanarl]

Hi, thanks for the help but could you make some changes or modifications of the code I updated my message at post #1.

[Mumps1]

Are you saying that if the PO No. in I7 already exists in the PO_VAULT sheet, you want all the data for that PO No. in the PO_VAULT sheet replaced with the current data in the PO sheet for that Po No.?

[archvanarl]

Are you saying that if the PO No. in I7 already exists in the PO_VAULT sheet, you want all the data for that PO No. in the PO_VAULT sheet replaced with the current data in the PO sheet for that Po No.?

PO No. could be anywhere at row 7 with an interval ir I7, V7, AI7, AV7...

It is okay not to be replaced because it may be too complicated because the data in each table with corresponding PO no. are stacked or collected there horizontally. Just a pop up msg that it is already existed and will cancel the function of the macro to avoid duplicate PO control nos in PO_VAULT.

If you propose to replace the data in that existing PO number at PO_VAULT, it's okay also but will also have a pop up of a YES or NO if you want to replace.. whichever is most possible for you sir.

[archvanarl]

Bump for this

[Mumps1]

Try:

Please Login or Register  to view this content.