copy and paste data to last used row wkbk

[Guest]

Hi,

I am trying to write a macro that copy data from one wksheet to another
master wksheet that i have. These masterwksheet as the names goes, has been
used for record tracking for years. How do i find the last unused rows of
that excelsheet and paste the copied data(from the new wksheet) and paste it
to that unused row.?

i tried with the following but it does not go to the last unused
range.Rather it points to no where. can someone help to see if my script was
wrong below:


Cells.Select
Selection.Copy

'To count the number of used rows and paste data to a new empty row.

nNewRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row

LastCell = Cells(Rows.Count, "A").End(xlUp)

Workbooks.Open Filename:="G:\Asia\Product\Operations\ML-Part
Adjustments\QtyCostXfer Log.xls" 'This is the mastersheet.

Windows("QtyCostXfer Log.xls").Activate

Cells(nNewRow, 2).Select 'Does not work.It fails here.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

 

[Dave Miller]

Sub SelectUnusedRow()
ActiveSheet.Range("A" & GetLastCell.Row).Select
End Sub

Public Function GetLastCell() As Range
Dim lRow, lCol As Long

lRow = GetLastRow
lCol = GetLastCol

Set GetLastCell = Cells(lRow, lCol)
End Function

Public Function GetLastRow() As Long
Dim l, lRow, lCell As Long

For l = 1 To xlLastCol
lCell = Cells(xlLastRow, l).End(xlUp).Row
If lCell > lRow Then lRow = lCell
Next l

GetLastRow = lRow
End Function

Public Function GetLastCol() As Long
Dim l, lCol, lCell As Long

For l = 1 To xlLastRow
lCell = Cells(l, xlLastCol).End(xlToLeft).Column
If lCell > lCol Then lCol = lCell
Next l

GetLastCol = lCol
End Function

 

[JW]

Once you open the mastersheet, that is when you need to find th last
used row. If the same column is always used, you could do something
like:
lastRow = Range("A65536").End(xlUp).Row
-or-
lastRow = Cells(65536, 1).End(xlUp).Row

If the data can be placed is different places throughout the sheet and
there is no set column that will always contain information, you could
use:
lastRow = ActiveSheet.UsedRange.Rows.Count
-or-
lastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

HTH
-Jeff-

 

[Dave Peterson]

Can you pick out a column that's always got something in it if that row is used?

I used column x in this sample:

Dim NextRow as long
with worksheets("summarynamehere")
nextrow = .cells(.rows.count,"x").end(xlup).row + 1
end with

Then I can use something like:

somerangetocopy.copy _
destination:=worksheets("summarynamehere").cells(nextrow,"A")

To paste into column A of the nextrow.