Macro to transfer data

[FredGersen]

Hi all,
I am looking for a Macro which can transfer data from sheet 1 to the first available empty row on sheet 2.
For example, I have data in the following cells on sheet 1: B2, B4, B6, B8, and B10. I want to transfer this data to the first available empty row into cells A1, B1, C1, D1 and E1 of sheet 2. The cell number is ofcourse depending on the first available empty row and could thus be 2, 3, 4, 5 etc.
I also want to delete the data from the original cells in sheet 1 after the data has been transfered.
Can anyone help me please? I am a beginner in Excel macro wisdom so be patient with me.

 

[VoG]

Try

Sub test()
With Sheets("Sheet1").Range("B2,B4,B6,B8,B10")
    .Copy
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    .ClearContents
End With
End Sub

 

[JMonty]

If I understand this correctly, you have a vertical array of data and you want to transpose it to another sheet as a row and delete the source data. Did I get it right?

EDIT: VoG posted a perfect and flexible solution. No need to answer me.

 

[FredGersen]

VoG,
Thanks for your amazingly quick reply. I have tested this and it works a charm. Now I have an idea how this works I will try to work it out on a bigger scale. If I need help I will shure post on this forum again. It's amazing. Thanks guys.
Never to old to learn or teach!

 

[Michael M]

Yeah...Peters pretty old !!!.....

 

[FredGersen]

Hi again guys and girls fo course,I have been playing with the avove VBA code for the transfer of data. I have however another problem. If I select other cells not being in the same colomn I get an error message in the macro. How can I do the same as my original question but with cells from different colomns. For example I want to do the same with cells B3,B4,B5, C1, C2,C3,B7,B8,B9. I want to show these cells in another worksheet (2) in the same order as stated in the first available row in the sequense as cells mentioned. Thanks in advance again.

 

[Michael M]

Because it's not a contigous block of data, you will have to break it up

With Sheets("Sheet1")
.Range("B3:B5").Copy Sheets("Sheet2").Range("B3")
.Range("C1:C3").Copy Sheets("Sheet2").Range("C1")
.Range("B7:B9").Copy Sheets("Sheet2").Range("B7")
End With

 

[VoG]

I think you'll need to loop

Sub ATest()
Dim LR As Long, i As Long, acells
acells = Array("B3", "B4", "B5", "C1", "C2", "C3", "B7", "B8", "B9")
With Sheets("Sheet2")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = LBound(acells) + 1 To UBound(acells) + 1
        .Cells(LR + 1, i).Value = Sheets("Sheet1").Range(acells(i - 1)).Value
    Next i
End With
End Sub

 

[FredGersen]

Thanks again guys,
The code from VoG worked for me but I have not yet tried the code Michael provided. Will for sure do this as well to get a better understanding of the various posibilities.
One more thing I want to find out. I have all the data on sheet 2 now and want on sheet 3 make a kind of look up table from the data on sheet 2. The way I would like to do that is by typing data from colomn A into cell A1 on sheet 3 and the next data from the colomn B on sheet2 into cell A2 on sheet3. What I then want to happen is that all following cells in colomn A on sheet3 automatically fill with the data from the row on sheet2 behind the 2 first cells in that row. I have the feeling this is going to be the most complecated part of this workbook. Thanks again!