copy data from one workbook to another-unknown problem

[dharsana]

hi
I am having a file "part".The data in "part" file is to be appended to another file "part-master" in the empty row.

part.xls
	A	B	C	D	E	F	G	H
1	SLOTNO	YEAR	NAME	STAFFNO	BRANCH	DESIG	CODENO	PROGRAM
2	200225	September-02	KUMAR	25789	BANG,JAYANAGAR	CLERK	1151	ALPM-W
3	200225	Sep-02	MOHAN	58963	BANG,BASAVANAGUDI	SPL.AAST	1151	ALPM-W
4	200226	Sep-02	MANI	25369	KALAKKOD	OFFICER	1152	ALPM-O
5	200226	Nov-02	MINI	52698	MANJERI	OFFICER	1152	ALPM-O
6	200227	Nov-02	SEKAR	25897	KOPPAL	MANAGER	1198	B2K-D-O
7	200227	Nov-02	VENU	45263	HARIHAR	MANAGER	1198	B2K-D-O
8	200225	September-02	KUMAR	25789	BANG,JAYANAGAR	CLERK	1151	ALPM-W
9	200225	Sep-02	MOHAN	58963	BANG,BASAVANAGUDI	SPL.AAST	1151	ALPM-W
10	200226	Sep-02	MANI	25369	KALAKKOD	OFFICER	1152	ALPM-O
Sheet1

part.xls
	A	B	C	D	E	F	G	H
1	SLOTNO	YEAR	NAME	STAFFNO	BRANCH	DESIG	CODENO	PROGRAM
2	200225	September-02	KUMAR	25789	BANG,JAYANAGAR	CLERK	1151	ALPM-W
3	200225	Sep-02	MOHAN	58963	BANG,BASAVANAGUDI	SPL.AAST	1151	ALPM-W
4	200226	Sep-02	MANI	25369	KALAKKOD	OFFICER	1152	ALPM-O
5	200226	Nov-02	MINI	52698	MANJERI	OFFICER	1152	ALPM-O
6	200227	Nov-02	SEKAR	25897	KOPPAL	MANAGER	1198	B2K-D-O
7	200227	Nov-02	VENU	45263	HARIHAR	MANAGER	1198	B2K-D-O
8	200225	September-02	KUMAR	25789	BANG,JAYANAGAR	CLERK	1151	ALPM-W
9	200225	Sep-02	MOHAN	58963	BANG,BASAVANAGUDI	SPL.AAST	1151	ALPM-W
10	200226	Sep-02	MANI	25369	KALAKKOD	OFFICER	1152	ALPM-O
Sheet1

I have put the follwing macro in part file and it works upto showing the message box with the empty row.Afterwards it is not copying the data to "part-master".If I change the destination file as Thisworkbook it is copying in the "part" file correctly and if I change the destination file to "part-master" it is not doing anything.
Any body can tell what mistake I am doing
Sub Macro2()
' Macro by sys
'
Dim LastRow As Long

'work with target workbook, changed to Book2 for testing
On Error Resume Next
Workbooks.Open Filename:="c:my documentspart-master"
With Application.Workbooks("c:mydocumentspart-master")

'find next blank row in target workbook
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
LastRow = LastRow + 1
MsgBox LastRow
End With
ThisWorkbook.Sheets(1).Rows("2:22").Copy _
Destination:=Workbooks("c:my documentspart-master").Sheets(1).Cells(LastRow, 1)
End Sub
Thanks in advance
dharsana

 

[JohnG]

Try this

Sub Test()
Dim LastRow As Long
'work with target workbook, changed to Book2 for testing
On Error Resume Next
Workbooks.Open FileName:="c:\my documents\part-master"
On Error GoTo 0
'find next blank row in target workbook
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Workbooks("c:\my documents\part-master").Sheets(1). _
Cells(65536, 1).Offset(xlUp).Rows + 1
Range(Workbooks("c:\my documents\part-master").Sheets(1).Cells(LastRow, 1)).Value = _
ThisWorkbook.Sheets(1).Rows("2:22").Copy
End If
End Sub

By the way you did not cancel your on error, so if there was an error later it would not show.

 

[dharsana]

hi john,

Thanks for the immediatereply .I copied your macro into "part" file (I do not understand why there are two forward slashes in filename(pathname) and I modified it to single slashes.If I execute the macro it gives syntax error at the following line

lastrow=workbooks("c:my documentspart-master").sheets(1)_

Any further help

dharsana