Automate the Copy & Paste process.

sur

Board Regular
Joined
Jul 4, 2011
Messages
176
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
Source Data - We need to Select the path.
sheet name - Sheet1
Destination - Sheets("Sheet1"), cell A1
 
Upvote 0
One final question: What is the full path to the folder containing the destination file?
 
Upvote 0
You can consider anything, i will change as per my requirement (please comment in red in the destination file to change).

Hope this helps
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top