I have inherited a spreadsheet that contains a macro and I do not know
how to use it. Could some body please explain it to me.
What I believe it should do is copy cells from "sheet1" to update
cells in "sheet2". To expand on this a little more sheet1 has 3
columns Date(A), Vol(B) and Value(C), these columns are populated from
an external source and are overwritten each month.
Sheet2 contains all of the Vols from sheet1 but is displayed with
Dates in ColA and the Vols are displayed by age going across the
spreadsheet. This results in a triangle shape. (the dates from sheet1
match to the dates in sheet2)
If you were to do this job manually, you would copy the Vol(B1) from
sheet1 and then go to the very end of sheet2 and paste it into a new
column(sayZ1), then go back to sheet1 and copy the next Vol(B2) into
sheet2 at (Y2) the after that to (X3) etc.
I was told that if I were to place the curser in sheet 1 and sheet2 in
the correct position and run the macro it will do the above for me. I
have tried various permutations and cannot get it to work . If I am in
sheet1 and run the macro all it does is delete the Vol column (and not
paste them anywhere else.) If I am in sheet2 and place the curser in
the next cell to be occupied, then run the macro all it does is add
"0" to the bottom of each column in sheet2.
Could somebody please tell me where the active cell should be on each
sheet and / or what the macro does.


Do Until ActiveCell.Offset(1, 0) = ""
Selection.Cut
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Selection.End(xlToRight).Select
Loop
Selection.Cut
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Do Until ActiveCell.Offset(1, -1) ""
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "0"
Loop
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.End(xlToRight).Select


End Sub

If? I understand what you want, this will, withOUT selections, from anywhere
in the workbook, cells b1:b3 to the destination sheet cells z1,y2,x3. Sheet
20 is the destination sheet and 27 is col Z. CursOr is spelled with an O.

Sub Copyshttosht()
With Sheets("sheet19") 'source sheet
For i = 1 To .Cells(Rows.Count, "b").End(xlUp).row
..Cells(i, "b").Copy Sheets("sheet20").Cells(i, 27 - i)
Next i
End With
End Sub

--
Don Guillett
SalesAid Software

"RJG" wrote in message
oups.com...
I have inherited a spreadsheet that contains a macro and I do not know
how to use it. Could some body please explain it to me.
What I believe it should do is copy cells from "sheet1" to update
cells in "sheet2". To expand on this a little more sheet1 has 3
columns Date(A), Vol(B) and Value(C), these columns are populated from
an external source and are overwritten each month.
Sheet2 contains all of the Vols from sheet1 but is displayed with
Dates in ColA and the Vols are displayed by age going across the
spreadsheet. This results in a triangle shape. (the dates from sheet1
match to the dates in sheet2)
If you were to do this job manually, you would copy the Vol(B1) from
sheet1 and then go to the very end of sheet2 and paste it into a new
column(sayZ1), then go back to sheet1 and copy the next Vol(B2) into
sheet2 at (Y2) the after that to (X3) etc.
I was told that if I were to place the curser in sheet 1 and sheet2 in
the correct position and run the macro it will do the above for me. I
have tried various permutations and cannot get it to work . If I am in
sheet1 and run the macro all it does is delete the Vol column (and not
paste them anywhere else.) If I am in sheet2 and place the curser in
the next cell to be occupied, then run the macro all it does is add
"0" to the bottom of each column in sheet2.
Could somebody please tell me where the active cell should be on each
sheet and / or what the macro does.


Do Until ActiveCell.Offset(1, 0) = ""
Selection.Cut
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Selection.End(xlToRight).Select
Loop
Selection.Cut
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Do Until ActiveCell.Offset(1, -1) ""
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "0"
Loop
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.End(xlToRight).Select


End Sub

If you placed the cursor in cell C2 and ran the macro, it
would delelte column B by overwriting it with the value of
column C before getting an error on the last loop because it
cannot set the Offset(1, -1) from column A.

As written, the macro is pretty much useless and was probably
a one time shot to do a specific manipulation.

"RJG" wrote:

I have inherited a spreadsheet that contains a macro and I do not know
how to use it. Could some body please explain it to me.
What I believe it should do is copy cells from "sheet1" to update
cells in "sheet2". To expand on this a little more sheet1 has 3
columns Date(A), Vol(B) and Value(C), these columns are populated from
an external source and are overwritten each month.
Sheet2 contains all of the Vols from sheet1 but is displayed with
Dates in ColA and the Vols are displayed by age going across the
spreadsheet. This results in a triangle shape. (the dates from sheet1
match to the dates in sheet2)
If you were to do this job manually, you would copy the Vol(B1) from
sheet1 and then go to the very end of sheet2 and paste it into a new
column(sayZ1), then go back to sheet1 and copy the next Vol(B2) into
sheet2 at (Y2) the after that to (X3) etc.
I was told that if I were to place the curser in sheet 1 and sheet2 in
the correct position and run the macro it will do the above for me. I
have tried various permutations and cannot get it to work . If I am in
sheet1 and run the macro all it does is delete the Vol column (and not
paste them anywhere else.) If I am in sheet2 and place the curser in
the next cell to be occupied, then run the macro all it does is add
"0" to the bottom of each column in sheet2.
Could somebody please tell me where the active cell should be on each
sheet and / or what the macro does.


Do Until ActiveCell.Offset(1, 0) = ""
Selection.Cut
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Selection.End(xlToRight).Select
Loop
Selection.Cut
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Do Until ActiveCell.Offset(1, -1) ""
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "0"
Loop
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.End(xlToRight).Select


End Sub