Automate the Copy & Paste process.

[sur]

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]

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?

 

[sur]

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]

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]

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]

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]

Source Data - We need to Select the path.
sheet name - Sheet1
Destination - Sheets("Sheet1"), cell A1

 

[mumps]

One final question: What is the full path to the folder containing the destination file?

 

[sur]

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]

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.

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