• 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.

macro to copy paste data between 2 excel files

J

joharmenezes

Member
I have 2 files in 2 different folders. I would like to know if there is a way to copy data from 1 of the files into another using only macros. I know it can be done using vba but I want to know if there is a way to do it purely by recording a macro...
 
Good day T100


Start your macro recorder,give it a meaningful name, select the data or column/area if you will be adding data and not updating, go to your second work book and select paste. Now turn of the macro recorder.


Go back to workbook N01 select a shape and when your are happy with where you have put it into one of the cells right click and choose Assign Macro and choose your named macro.


Now when you have entered data into the column/area just hit the shape for the data to be copied to workbook No2.
 
Good day T100


Try this VBA, you may need to change some settings.

[pre]
Code: 
Sub CopyOpenItems()
'
' CopyOpenItems Macro
' Copy open items to sheet.
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Dim wbTarget            As Workbook 'workbook where the data is to be pasted
Dim wbThis              As Workbook 'workbook from where the data is to copied
Dim strName             As String   'name of the source sheet/ target workbook 

'set to the current active workbook (the source book)
Set wbThis = ActiveWorkbook 

'get the active sheetname of the book
strName = ActiveSheet.Name 

'open a workbook that has same name as the sheet name
Set wbTarget = Workbooks.Open("C:filepath" & strName & ".xlsx") 

'select cell A1 on the target book
wbTarget.Range("A1").Select 

'clear existing values form target book
wbTarget.Range("A1:M51").ClearContents 

'activate the source book
wbThis.Activate 

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False 

'copy the range from source book
wbThis.Range("A12:M62").Copy 

'paste the data on the target book
wbTarget.Range("A1").PasteSpecial 

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False 

'save the target book
wbTarget.Save 

'close the workbook
wbTarget.Close 

'activate the source book again
wbThis.Activate 

'clear memory
Set wbTarget = Nothing
Set wbThis = Nothing 

End Sub
[/pre]
I am not the author of this code but have been told that it has been tested and it works.
 
yes but again this requires vba. If u read my first post, I have said that vba shouldn't be used. So again is this possible? thanks
 
T100


I did read your first post and I am not sure as to what you mean, macros are VBA, if you insert a module in the the VBA editor and then insert the VBA code you can assign the macro to a button or short cut keys to run as you need.
 
Hi, T100!

And if you just want to do something in Excel recording a macro, why don't you directly record that macro? Almost anything that can be done in Excel can be recorded into a macro as you're doing it. Have you tried and found any issue?

Regards!
 
Hi SirJB7 I tried recording a macro but it seems it can't open a closed workbook and copy the data while the macro is being recorded. I have to open the workbook first and then record the macro to copy the contents.


I have modifed my above question a bit which I had posted in a new thread but bobhc has advised to post it in the same thread as it's similar to this one. So here it goes:


I have 2 workbooks book1 http://www.filedropper.com/book1_9 and book2 http://www.filedropper.com/book2_2 . I have a button in book1 which when clicked should import rates from book2 and paste them in book1 corresponding to the items in book1. it should then calculate the amount (qty*rates) and then sort the data in descending order of the amount. book1 and book2 could be in different folders and book2 need not be opened while book1 is open. the number of items/qty in book1 could increase or decrease and would be manually entered.
 
Hi, T100!


Your first file is a .xlsx file so it can't contain macros.


Despite of that I did this and it worked:


a) Downloaded 1st file and saved it as a .xlsm file


b) Downloaded 2nd file, saved it and closed it


c) Start recording the following macro in 1st file:

-----

[pre]
Code: 
Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
Workbooks.Open Filename:="C:<path>Book2.xlsx"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.Close
Range("A2").Select
ActiveSheet.PasteSpecial Format:="Texto Unicode", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Range("A2").Select
End Sub
[/pre]
-----

This is the code just as generated by the macro recorder, exception made of the path. You weren't able to make your Excel record an alike macro?


If not, try this from the 1st file while 2nd is closed:

a) Start recording macro

b) File, Open, select 2nd workbook

c) Select A2 cell, end right, end down

d) Copy selection

e) Close active workbook

f) Select A2 cell

g) Paste selection

h) Select A2 cell

i) Stop recording macro


Regards!
 
Hi SirJB7 I tried doing exactly what u said. I have saved book1.xlsx as xlsm. But I don't get this step, Workbooks.Open Filename:="C:<path>Book2.xlsx" when I checked the vba window.


Here's what I get:


Sub Macro5()

'

' Macro5 Macro

'


'

Range("A1:B12").Select

Selection.Copy

Windows("Book1.xlsm").Activate

Sheets("Sheet2").Select

Range("A1").Select

ActiveSheet.Paste

Application.CutCopyMode = False

ActiveWorkbook.Save

Windows("Book2.xlsx").Activate

ActiveWindow.Close

End Sub
 
Hi, T100!

Strange thing, as I wrote down each step as I were doing them. Are you sure you strictly followed the guidelines? I think you didn't as the 1st step is opening a closed workbook (which isn't done in your code), then selecting a range using End+arrow keys (which you seem to have done selecting manually a fixed range), and... all the steps with differences.

Please close Excel, open your first workbook, follow the instructions without any change, and then check the result.

Regards!
 
SirJB7 I am really sorry. I didn't read step b) carefully. I was supposed to open the workbook from within the active workbook by going to File-->Open. I was opening it directly by double clicking on the file on my desktop. Thank you so much problem resolved.
 
hi use can use the below code and select the path when it asks (where data) and the same in store in the current file where you have store this below macro code


Sub console()

Dim x As Integer

Dim wbk As Workbook

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True

.Show

For x = 1 To .SelectedItems.Count

Set wbk = Application.Workbooks.Open(.SelectedItems(x))

Range("A2").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

ThisWorkbook.Activate

Range("A2").Select

ActiveSheet.PasteSpecial

Next x

wbk.Close (1)

End With

End Sub
 
Hi, T100!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top