• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Move Only The Selected Data From 1 WorkBook sheet1 to Another Already Workbook

Kindly check... the target Path and the Book Name...when the targetpath book is opened so it transfers the data!...when its not so it gives error.."Run-time error '1004':

'C:Documents and SettingsmintezarMy DocumentsArchivesSampleA.xlsA.xls' could not be found.


Sub MoveStuff()

Dim sourceBook As Workbook

Dim targetBook As Workbook

Dim targetPath As String

Dim BookName As String

Dim NeedOpen As Boolean


'make easy reference names for both the book data is coming FROM and going TO

Set sourceBook = ActiveWorkbook

'set the target workbook's filepath as a variable so it is easy to change later

targetPath = "C:Documents and SettingsmintezarMy DocumentsArchivesSampleA.xls"

BookName = "A.xls"

'copy the selected range

Selection.Copy


'Check if workbook is already opened

NeedOpen = True

For Each wb In Application.Workbooks

If wb.Name = BookName Then

NeedOpen = False

Exit For

End If

Next


'~~~~~Beginning of edit, next line may need to be changed

Selection.Copy


If NeedOpen Then

'open the target book

Workbooks.Open targetPath & BookName, Format:=2 'Forgot to include BookName :(

Else

Workbooks(BookName).Activate

End If


'make easy reference names for both the book data is coming FROM and going TO

Set targetBook = ActiveWorkbook


'paste info into A1 on sheet 1

targetBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


'~~~~~~End of edit


MsgBox "Data transfer complete.", vbInformation, "Process Complete"

End Sub
 
The Error is : Run time error '1004'

'C:Documents and SettingsmintezarMy DocumentsArchivesSampleA.xlsA.xls' could not be found...


i have given targetpath as and BookName as A.xls

targetPath = "C:Documents and SettingsmintezarMy DocumentsArchivesSampleA.xls"

BookName = "A.xls"


when the target path is opened so it transfers the data successfully..
 
The target path should only have the path name, not the workbook name as well.

targetPath = "C:Documents and SettingsmintezarMy DocumentsArchivesSample"

BookName = "A.xls"
 
You must log in or register to reply here.
Back
Top