Help with VBA

[s7yz]

hello everybody, I have Sheet1 where data is input in rows: cell A = weight, cell B = ULD, cell C = Dest, cell D = Height and cell E = Specials. Now I would like to have a function that allows me, with F1, to select the row and transport the data to Sheet!2 (or whatever the name of the sheet might be) and with F2 to insert the data. This time the data should be inserted in a vertical way, so from the cell that I select (i.e A12) down till A15 (cell D is not necessary). The order should be A12 = ULD, A13 = Weight, A14 = Dest, A15 = Specials.

Is this possible?

thank you all

[zeddy]

Hi

Welcome to the Lounge.
Yes, you can use VBA to do what you want.
But you can't really assign keys [F1] and [F2] for your macros as these are really already used for [Help] and [Edit] which could cause confusion.

So, in the attached workbook I used keys
Ctrl-Shift-C for the copy part, and, after you move to the required cell on the other sheet,
Ctrl-Shift-P to Paste the values as required in the required order you specified.

zeddy

[s7yz]

Hi Zeddy, that is exactly what I was looking for. Can I add a couple of features to the paste rules?

[zeddy]

Hi

Of course you can.
What did you have in mind?

If you need any further help, let us know.
I will check here again tomorrow.

zeddy

[s7yz]

I hope I can make my point: the destinations cells in Sheet1 (where I paste the data) have borders and are all adjacent. Suppose that in sheet2 (the source) in the ULD cell there is a value beginning with AKG. It would be great if when I paste this values in Sheet1 ONLY FOR VALUE that have a certain prefix (like AKG, BKG and TKG) the destination cells could merge (if I choose F1 this should merge with G1 and so should the other 3 cells below that), the values centered, the weight value should be divided by 2 between the destination cells but the visible value should be the one in sheet2 (i.e I choose a weight of 10000lbs, I put it in F1 and G1 and the value in F1 should be 5000 and the same in G1 but what you see is 10000).

Hope this makes any sense... lol

[s7yz]

Hello Zeddy, I have a different question: I can use the code on my sheet only if I open yours first and I keep it running. If I don't do that it will not work.

[zeddy]

Hi

First of all, I do not think it is easily possible to have the weight split into two cells as, say 5000 and 5000, but display 10000.

Second, the short-cut-keys Ctrl-Shift-C and Ctrl-Shift-P are assigned to macros in the workbook I sent you.
If you want these shortcuts to work in your own workbooks (i.e. without loading my workbook) then you need to first copy the VBA module from my workbook into your workbook, and then to assign the shortcuts to these macros in your workbook (using Tools>Macro>highlight macro in list>then click [Options] button, then assign shortcut key e.g. Ctrl+Shift+C etc)

zeddy

[s7yz]

Oh thank you very much for this explanation... is it possible to make it impossible to place twice the same row (avoid duplicates) and also, to create some kind of swap code that allows me to swap cells (say I select A1, then the values in A1, A2, A3 and A4 should be selected with some shortcut keys, then I select the destination cell and if this is full, whatever is there should go in A1 A2 A3 and A4 and replaced by what was in A1 A2 A3 and A4)... messy?

Thank you very much for your inputs

[zeddy]

Hi

In your copy-transpose process, it is possible to prevent the same row from being copied.

This could work by placing a marker to say that a particular row has already been copied.

For example, if data from row 7 has already been copied and then transposed elsewhere, we could put a value of "done" or say, numeric value of 1, in a cell in the same row, e.g. cell [F7].
When we press Ctrl-Shift-C, we then first check to see if the value in column [F] of this row has our defined marker.
If it does, then we can display a message saying this data row has already been copied.
If there is no marker for the current row, then we continue with the Ctrl-Shift-C macro, saving the row number for later use.

When we press Ctrl-Shift-P to transpose and paste the data, we include a new piece of code to place the marker in the row that has been copied.
In this situation, the only way to copy the same data again, would be to delete the marker in that row.

The attached file demonstrates this.

I have used column [F] on [Sheet1] for the marker, and I have used a marker of "1" (so that I could add up all the records that have been copied, for example).

The second part of your question:
You need to be more precise as to what you want.
What you mean by destination cell is full?
Do you mean that the destination cell is not empty?
And do you mean that if specifically the selected destination cell is not empty, then, no matter what, you want the four related 'destination' cells to now be copied back to replace the original four 'source' cells.

I shall be away for a week so perhaps other helpers here can assist.

zeddy

[s7yz]

Hello Zeddy, with "destination cell is full" I mean that I have already input data. What I would like to achieve is to be able to swap values between cells

[zeddy]

Hi

See attached file.

On sheet [source1], I have placed some sample data.
Use Ctrl-Shift-B to copy a Block of 4cells.
Use Ctrl-Shift-D to paste this block Down at a destination cell.

If the destination cell is not empty, then the 4 cells at the destination will be copied back to replace the 4 cells at the location where you pressed Ctrl-Shift-B

I believe this does everything you asked for.

zeddy

[s7yz]

hello Zeddy, I have tried the program but there is a problem: say I select the data in A1 (automatically I will get A1:A4). I then select C5 and paste. All data that was in A1:A4 will then be in C5:C8 but I need the data that was in C5:C8 to be in A1:A4.

[zeddy]

Hi

I assumed that you would have headings in row 1, so this is the only row that is ignored for the copy process.
Try it on any other row.

If you really want to allow row 1 to be included, simply comment out that section that tests for row 1 in the VBA code (see module2).

zeddy

[s7yz]

hello Zeddy, can I ask you another question about the problem?

[zeddy]

Hi

Yes. Ask your question.

zeddy