PDA

View Full Version : Select All Data



maninjapan
09-30-2008, 02:52 AM
Im using the following to transfer data from a temporary sheet to the main spreadsheet. Im using the following Code. Currently I use a fixed range to cut but as my list of data may vary ( and there may be empty cells) I would like the range to be A1:'Last Row with Data in it

Sub CopyPaste()

Sheets("Sheet4").Select
Range("A3:I16").Select
Selection.Cut
Sheets("Sheet1").Select
NextRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(NextRow + 2, 1).Select
ActiveSheet.Paste

End Sub


Thanks in Advance

GTO
09-30-2008, 04:02 AM
I used the default sheet codenames, but you could use the 'Worksheets("Sheet1")' as well. Either way, 'CurrentRegion' would be one way to get the last row or column.

Option Explicit
Sub TransData()
Dim intRows As Integer, intCol As Integer

intRows = Sheet4.Range("A1").CurrentRegion.Rows.Count
intCol = Sheet4.Range("A1").CurrentRegion.Columns.Count

Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(intRows, intCol)).Value = _
Sheet4.Range(Sheet4.Cells(1, 1), Sheet4.Cells(intRows, intCol)).Value

End Sub

maninjapan
09-30-2008, 04:22 AM
I just tried it as is, but it came back with a compiler error. says variable not defined...

Bob Phillips
09-30-2008, 04:46 AM
Sub CopyPaste()
Dim sh1 As Worksheet
Dim NextRow As Long

Set sh1 = Worksheets("Sheet1")
With Sheets("Sheet4")

NextRow = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row
Range(.Range("A3"), .Range("A3").End(xlDown)).Resize(, 9).Cut sh1.Cells(NextRow + 2, 1)
End With
End Sub

maninjapan
09-30-2008, 04:51 AM
thanks XLD, just tried that but it only copied down to the first gap.
There are goingto be gaps in the data.
I could just make the range big enough to ensure it would pick up even my largest bit of Data. ( I roughly know the largest possible size I would need)
I was just thinking if there is a proper way to do it without too much hassle....

Bob Phillips
09-30-2008, 04:58 AM
Sub CopyPaste()
Dim sh1 As Worksheet
Dim LastRow As Long
Dim NextRow As Long

Set sh1 = Worksheets("Sheet1")
NextRow = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row
With Sheets("Sheet4")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A3").Resize(LastRow - 2, 9).Cut sh1.Cells(NextRow + 2, 1)
End With
End Sub

GTO
09-30-2008, 05:23 AM
Then you had other procedures in the same module that didn't have variable(s) dim'd.

Just take out the 'Option Explicit'

I like Bob's second one though, as I don't oft use 'Cut'.

Here's with using Worksheet (tab) names.

I'm out...


Sub TransData_2()
Dim intRows As Integer, intCol As Integer

intRows = Worksheets("Sheet4").Range("A1").CurrentRegion.Rows.Count
intCol = Worksheets("Sheet4").Range("A1").CurrentRegion.Columns.Count

Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(1, 1), Worksheets("Sheet1").Cells(intRows, intCol)).Value = _
Worksheets("Sheet4").Range(Worksheets("Sheet4").Cells(1, 1), Worksheets("Sheet4").Cells(intRows, intCol)).Value

End Sub

maninjapan
09-30-2008, 05:50 AM
GTO, thanks for that. Its always good to see different ways of seeing things.
XLD, one small problem, Its not copying over the last row of data. Column A in the the last row of data to be transferred over is empty, the data in the last row start from about column E.

Bob Phillips
09-30-2008, 07:03 AM
Sub CopyPaste()
Dim sh1 As Worksheet
Dim LastRow As Long
Dim NextRow As Long

Set sh1 = Worksheets("Sheet1")
NextRow = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row
With Sheets("Sheet4")

LastRow = GetLastRow
.Range("A3").Resize(LastRow - 2, 9).Cut sh1.Cells(NextRow + 2, 1)
End With
End Sub

'-----------------------------------------------------------------
Function GetLastRow() As Long
'-----------------------------------------------------------------
GetLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function