Solved: moving data from sheet1 to sheet2

[Tenspeed39355]

Good morning guys. I have some data in sheet 1 Column BH. There are spaces between the data in column BH. Some of the data might be in BH6 and then the next data is in BH 30 etc. I need to move all the data in Sheet 1 column BH to sheet 2 column E5. I need the data to be in the same order as it was in sheet 1. The data in sheet 2 column E will run from E5:E20. Can you help me with this??
Thanks for your time with my problem.
Max

[OBP]

Do you want Sheet 2 Column E5 to have the spaces in or not?

[Tenspeed39355]

No spaces in E5:E20
Max

[OBP]

With so few rows to "move" is it worth writing VBA to do it?
Do you want them moved, ie deleted form sheet 1 or just copy and pasted?

[lucas]

[VBA]Range("BH1:BH43").Select
Selection.Copy
Sheets("Sheet2").Select
Range("E5").Select
ActiveSheet.Paste
Range("G10").Select
Sheets("Sheet2").UsedRange.SpecialCells(xlBlanks).EntireRow.Delete[/VBA]

[snicho]

If you really have to do this in VBA then the following should come close...

[vba]Sub CopyColValues()
Dim myArray As Variant
Dim lastRow As Integer
Dim rngCell As Variant
Dim n As Integer

'determine last row containing data
lastRow = Cells(1000, Range("BH1").Column).End(xlUp)

'resize myArray
ReDim myArray(lastRow)
n = 0

For Each rngCell In Range("BH1", Range("BH1").Offset(lastRow, 0))
If rngCell.Value <> 0 And _
Not IsEmpty(rngCell.Address) Then
myArray(n) = rngCell.Value
n = n + 1
End If
Next rngCell
ReDim Preserve myArray(n - 1)

'write values to worksheet
Sheet(2).Range("E5:E" & 5+n).Value = Application.Transpose(myArray)

End Sub
[/vba]

Steve

[Tenspeed39355]

Thanks for your time with my problem. You have solved the problem
Max