copy and paste data to last used row wkbk
 

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

copy and paste data to last used row wkbk
 

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

copy and paste data to last used row wkbk
 

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-

Junior728 wrote:
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

copy and paste data to last used row wkbk
 

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(n extrow,"A")

To paste into column A of the nextrow.

Junior728 wrote:

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 Peterson