Macro to copy from one sheet to another

[yioryos467]

Hi All,

Can someone please help me with a macro....

What I need to do is copy from sheet1 A33 - A40 to sheet 2, to the next available row col A-H

Values in A33- A40 are numbers and change twice a week and I need to run the macro and copy those numbers in columns A-H, on the next available row in sheet2, as I want to keep the history.

Thank You
George

 

[Tim_Excel_]

Hi All,
...copy from sheet1 A33 - A40 to sheet 2, to the next available row col A-H

Do I understand correctly you want to copy A33:H40 in sheet 1?

 

[yioryos467]

Hi Tim.

From sheet 1
Column A only, rows 33-40

To sheet 2
Columns A-H, the next available row... (it could be row 200. so next time I run the macro it copies to row 201. then 202 and so on)


So A33 (sheet1) to column A (sheet2)
A34 (sheet1) to column B (sheet2)
A35 (sheet1) to column C (sheet2)
A36 (sheet1) to column D (sheet2)
A37 (sheet1) to column E (sheet2)
A38 (sheet1) to column F (sheet2)
A39 (sheet1) to column G (sheet2)
A40 (sheet1) to column H (sheet2)

Hope this helps
George

 

[Tim_Excel_]

Hi George. The word you're looking for is "transpose", which is essentially what you're doing by transforming vertical data into a horizontal orientation.

Paste this into a module:

Sub transpdata()


LRow = ThisWorkbook.Worksheets(2).Cells(ThisWorkbook.Worksheets(2).Rows.Count, "A").End(xlUp).Row

Worksheets(1).Range("A33:A40").Copy
LAddress = Cells(LRow + 1, 1).Address(0, 0)
Worksheets(2).Range(LAddress).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

 

[yioryos467]

Hi Tim,

I ran the macro and it comes up with, Select destination and press ENTER to paste....
the result I get is:-

1
1
1
1
1
1
1
1


rather than pasting the cell values of A33-A40.

I would like if it automatically does it and pastes into the next available row in sheet2, across columns A-H.

Thanks
George

 

[Tim_Excel_]

George,

I've just tested the code again, and it ran fine without any problems.

Did you paste the code in a new module?
Are the references correct?

The code copies cells A33:A40 from the first sheet in the workbook and transposes these into the last row in the second sheet of the workbook. If your references are correct, it should not only pick up 1's and put these in a column. Perhaps you can send a screen grab of your workbook?

 

[Peter_SSs]

I have also tested Tim's code and it works fine for me too, provided (as he indicated in post 6) that Sheet1 is the left hand sheet in the workbook and Sheet2 is second from the left.
There is also an inbuilt assumption that there is always something in cell A33 of Sheet1. If not, some old data in Sheet2 may get over-written, but not the sorts of issues you have mentioned.

In case the order of the sheets is the issue, you could also try this code in a copy of your workbook.

Sub CopyAndTranspose1()
  Sheets("Sheet1").Range("A33:A40").Copy
  Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Transpose:=True
  Application.CutCopyMode = False
End Sub

And if you are really only interested in the values being copied (& not formatting etc) then you could try this single-liner.

Sub CopyAndTranspose2()
  Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 8).Value = Application.Transpose(Sheets("Sheet1").Range("A33:A40").Value)
End Sub

 

[My Aswer Is This]

Tim's script works properly when I use it.

 

[yioryos467]

Entered the code in a clean sheet and it worked fine.

Thank You Tim

Also Peter, your code worked well as well. Thank you too.

Regards
George

 

[Peter_SSs]

Entered the code in a clean sheet and it worked fine.

Thank You Tim

Also Peter, your code worked well as well. Thank you too.

Regards
George

Glad you got the codes working. Thanks for letting us know.