Home » Forums » Open Source Learning Center » Question and Answer

Copy Data from Closed File to Existing File

Submitted by srikanthbenoni on 18 May, 2012 - 14:02

I have created a macro for following functions.

1. Open the Workbook
2. Copy data (from C12 : D12 to Active cells)
3. Paste the data in already opend sheet. Paste from the next empty cells.

When am using the macro in Module, It is Working, however when i have assigned the Button, it is not working and showing at

"Range("C12:D" & DLsummary).Select"

Error message " Run Time Error '1004' Select method of Range Class Faild (Value of "1048576")

Complete Code is below:

Private Sub CommandButton1_Click()
Dim strcheminFichier As String
Dim Name_File As String
Dim LoadFile As Variant
Dim Workbook_Name As String
Dim x
Dim Y
Workbook_Name = ThisWorkbook.Name
strcheminFichier = Application.GetOpenFilename("xls File (*.xls), *.xls", , "Select PPV Oracle report", , False)
If strcheminFichier = "False" Then
MsgBox "Please select file"
Exit Sub
End If
LoadFile = Split(strcheminFichier, "\")
Name_File = LoadFile(UBound(LoadFile))
Workbooks.Open Filename:=strcheminFichier

Windows(Name_File).Activate
DLsummary = Range("A12").End(xlDown).Row
Sheets("Summary").Select
Range("C12:D" & DLsummary).Select
Selection.Copy

Windows(Workbook_Name).Activate
Sheets("Rates").Select
nbLigne = Range("A65536").End(xlUp).Row
Range("B" & nbLigne + 1).Select
ActiveSheet.Paste

Windows(Workbook_Name).Activate
Windows(Name_File).Activate
ActiveWindow.Close
End Sub

AttachmentSize
Test Files.zip8.85 KB
‹ extract the object from multiple closed workbook and paste into open workbook. Script won't run in Excel 2010 ›
»
Vishesh's picture

Try changing the sequence of

Submitted by Vishesh on 18 May, 2012 - 17:12.

Try changing the sequence of statements like
.
.
Windows(Name_File).Activate
Sheets("Summary").Select
DLsummary = Range("A12").End(xlDown).Row
Range("C12:D" & DLsummary).Select
Selection.Copy
.
.
Upload the file if this doesn't work

»

File Attached

Submitted by srikanthbenoni on 21 May, 2012 - 07:38.

Hi, Thanks for your reply. As requested i have uploaded the file,

»
Vishesh's picture

Here is the modified

Submitted by Vishesh on 21 May, 2012 - 14:56.

Here is the modified code...

Private Sub CommandButton1_Click()
Dim strcheminFichier As String
'Dim Name_File As String
Dim LoadFile As Variant
'Dim Workbook_Name As String
Dim DLsummary As Integer
Dim nbLigne As Integer
Dim wbkMe As Workbook
Dim wbkOpen As Workbook
'Dim x
'Dim Y

'Workbook_Name = ThisWorkbook.Name
Set wbkMe = ThisWorkbook
strcheminFichier = Application.GetOpenFilename("xls File (*.xls), *.xls", , "Select PPV Oracle report", , False)
If strcheminFichier = "False" Then
MsgBox "Please select file"
Exit Sub
End If
LoadFile = Split(strcheminFichier, "\")
'Name_File = LoadFile(UBound(LoadFile))
Set wbkOpen = Workbooks.Open(Filename:=strcheminFichier)

'Windows(Name_File).Activate
DLsummary = wbkOpen.Worksheets("Summary").Range("A12").End(xlDown).Row
'Sheets("Summary").Select
wbkOpen.Worksheets("Summary").Range("C12:D" & DLsummary).Copy
'Selection.Copy

'Windows(Workbook_Name).Activate
'Sheets("Rates").Select
nbLigne = wbkMe.Worksheets("Rates").Range("A65536").End(xlUp).Row
'Range("B" & nbLigne + 1).Select
wbkMe.Worksheets("Rates").Range("B" & nbLigne + 1).PasteSpecial xlPasteAll
'wbkMe.ActiveSheet.Paste

'Windows(Workbook_Name).Activate
'Windows(Name_File).Activate
'ActiveWindow.Close
wbkOpen.Close False

Set wbkMe = Nothing
Set wbkOpen = Nothing
End Sub

»

Thanks

Submitted by Anonymous on 22 May, 2012 - 07:42.

Thanks alot for the modification of the Code. Its works for me

»