PDA

View Full Version : Solved: moving data from sheet1 to sheet2



Tenspeed39355
07-07-2006, 06:22 AM
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
07-07-2006, 06:50 AM
Do you want Sheet 2 Column E5 to have the spaces in or not?

Tenspeed39355
07-07-2006, 06:56 AM
No spaces in E5:E20
Max

OBP
07-07-2006, 07:14 AM
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
07-07-2006, 07:39 AM
Range("BH1:BH43").Select
Selection.Copy
Sheets("Sheet2").Select
Range("E5").Select
ActiveSheet.Paste
Range("G10").Select
Sheets("Sheet2").UsedRange.SpecialCells(xlBlanks).EntireRow.Delete

snicho
07-07-2006, 08:27 AM
If you really have to do this in VBA then the following should come close...

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


Steve

Tenspeed39355
07-07-2006, 05:55 PM
Thanks for your time with my problem. You have solved the problem
Max