PDA

View Full Version : [SOLVED] How to replace/move data from ons sheet into a workbook in a specific sheet.



Jagdev
04-28-2015, 03:08 AM
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
04-28-2015, 05:38 AM
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
04-28-2015, 06:07 AM
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
04-28-2015, 09:47 AM
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
04-29-2015, 02:32 AM
Hi Yongle

Thanks for the code. It fits into my requirement.

Regards,
JD