How to replace/move data from ons sheet into a workbook in a specific sheet.

[Jagdev]

Hi Experts,

I am not sure how to proceed with this query. I have a sheet with specific information and I want to move its data in another workbook and in specific sheet name "Data". Is it possible? If yes, please help me with the code.

Regards,
JD

[Yongle]

Run this from the sheet you want to copy from
Amend D:\Documents\workbook2.xlsx to the full file name and path of the file to copy to
This code copies everything from the active sheet, opens the other file, pastes values and formats into sheet named Data, saves and closes that file

Sub CopyToAnotherWorkbook()

Dim MyFile As String
MyFile = "D:\Documents\workbook2.xlsx"

With ActiveSheet
.Cells.Copy
End With

Workbooks.Open Filename:=MyFile
Sheets("Data").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("Data").Range("A1").PasteSpecial Paste:=xlPasteFormats

ActiveWorkbook.Close True

End Sub

To paste everything including formulas, delete the 2 PasteSpecial lines and replace with a single line to paste everything

Sheets("Data").Range("A1").PasteSpecial Paste:=xlAll

[Jagdev]

Hi Yongle

Thanks for the code. It is working fine. Sorry for troubling you little more. I will be saving the file where we have to save the data in a new folder everytime and is it possible for the code to pick the excel file from the folder. The reason I am asking this because the file name will always change, but the operation to perform will be same.

Example this line - MyFile = "D:\Documents\workbook2.xlsx"

Can we use it as Myfile = Inputbox("Enter the folder path")

The folder will contain only one file and code should pick this file and it will have Data sheet for sure in it.

Is this possible

Regards,
JD

[Yongle]

Try this.
I have included variable DefaultPath - I assume that the first bit of your file path will always be the same. Amend the "D:\Documents\" to reflect that and then you will only need to add the last bit of the path each time in the input box.
This will open the first excel file it comes across in the folder specified

Sub CopyToAnotherWorkbook2()
     
    Dim MyFile As String
    Dim DefaultPath As String, MyPath As String, Filename As String, Directory As String
    DefaultPath = "D:\Documents\"
    MyPath = InputBox("Enter folder path with trailing \", "Folder to Save Data", DefaultPath)
    
    With ActiveSheet
        .Cells.Copy
    End With
     
    Directory = MyPath
    Filename = Dir(Directory & "*.xl??")
    Workbooks.Open (Directory & Filename)
   
   Sheets("Data").Range("A1").PasteSpecial Paste:=xlPasteValues
   Sheets("Data").Range("A1").PasteSpecial Paste:=xlPasteFormats
     
   ActiveWorkbook.Close True
     
End Sub

NB - do not forget to include the trailing \ when you input the folder path

[Jagdev]

Hi Yongle

Thanks for the code. It fits into my requirement.

Regards,
JD