Automate the Copy & Paste process.

sur

Board Regular
Joined
Jul 4, 2011
Messages
164
Hello Team,


need to automate the process where in i am currently copy paste the data from one file to another file.


Needed in macro.


1. Select the file that needs to copied (Source File).
2. Copy the all the cells, which have data.
3. Open the Destination File, last + 1 row & paste the source data.
3. save the destination File & close.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,535
Are both the source and destination files closed and do they need to be opened by the macro? If they are closed, you will need a third file to run the macro. Will the source and destination files always be the same or can they vary? What is the name of the sheet containing the source data and the name of the sheet in the destination file where the data will be pasted?
 
Last edited:

sur

Board Regular
Joined
Jul 4, 2011
Messages
164
1. Both the Files are closed.
2. Destination File Name based on the Cell value in the Macro Workbook.
3. Source File to Select the path or drag and drop the file to macro.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,535
I assume that by "Macro Workbook" you mean the third workbook which will contain the macro (not the source or destination file). If that is correct, what is the sheet name and the range that contains the name of the destination file and what is the full path to the folder containing the destination file? Does the cell containing the name of the destination file contain the full name of the file including the extension (xlsx, xlsm)? Also, what is the name of the sheet containing the source data and the name of the sheet in the destination file where the data will be pasted? In order to suggest a possible solution, I need clear answers to allthese questions.
 

sur

Board Regular
Joined
Jul 4, 2011
Messages
164
Hi

Sorry for the delay,

1. Yes "Macro Workbook", the third workbook.
2. Destination file - to selected form the same workbook (Macro Workbook) cell, where i will be changing based on the requirment.
3. Destination Cell - will contain with name.xlsx
4. Source dat is the excel file, which to be selected with the path.(Names may wary)
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,535
I'm sorry but I still don't have a clear picture of where your source data is located and where you want to paste it. What is the sheet name and address of the cell in the Macro Workbook that contains the name of the destination file, for example, Sheets("Sheet1"), cell A1. What is the name of the sheet that contains the data you want to copy in the source file?
 

sur

Board Regular
Joined
Jul 4, 2011
Messages
164
Source Data - We need to Select the path.
sheet name - Sheet1
Destination - Sheets("Sheet1"), cell A1
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,535
One final question: What is the full path to the folder containing the destination file?
 

sur

Board Regular
Joined
Jul 4, 2011
Messages
164
You can consider anything, i will change as per my requirement (please comment in red in the destination file to change).

Hope this helps
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,535
Place this macro in the third workbook and run it from there. Change the folder path of the destination file (in red) and the location of the destination file name (in blue) to suit your needs.
Code:
Sub CopyData()
    Application.ScreenUpdating = False
    Dim WS As Worksheet, desWB As Workbook, srcWB As Workbook, flder As FileDialog, FileName As String, FileChosen As Integer
    Set WS = ThisWorkbook.Sheets("Sheet1")
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    With flder
        .Title = "Please Select an Excel File."
        .InitialFileName = "c:\"
        .Filters.Clear
        .Filters.Add "Excel Macros Files", "*.xlsx"
    End With
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set srcWB = Workbooks.Open(FileName)
    Sheets("Sheet1").UsedRange.Copy
    Set desWB = Workbooks.Open("[COLOR="#FF0000"]C:\Test\[/COLOR]" & WS.[COLOR="#0000FF"]Range("A1")[/COLOR])
    Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
    desWB.Close True
    Application.ScreenUpdating = True
End Sub
 

Forum statistics

Threads
1,082,336
Messages
5,364,701
Members
400,811
Latest member
MSBINinja

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top