Copy paste data from one workbook to another

[Sarthakjaiswal44]

Hello

I am a starter in VBA.

I have one workbook by the name copy.xlsm, it has a data in "Sheet1" from A1:J14.

Now i wish to copy this data to different workbooks, whenever required, Therefore I cannot set the destination workbook name beforehand, but the destination worksheet will have "Sheet2" where data has to be pasted.

Please help me out with a simple VBA code.

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim flder As FileDialog
    Dim FileName As String, FileChosen As Integer
    Dim wkbDest As Workbook, wkbSource As Workbook
    Set wkbSource = ThisWorkbook
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Please Select a Excel Macro File"
    flder.InitialFileName = "c:\"
    flder.InitialView = msoFileDialogViewSmallIcons
    flder.Filters.Clear
    flder.Filters.Add "Excel Macros Files", "*.xls*"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set wkbDest = Workbooks.Open(FileName)
    wkbSource.Sheets("Sheet1").Range("A1:J14").Copy wkbDest.Sheets("Sheet2").Range("A1")
    wkbDest.Close savechanges:=True
    Application.ScreenUpdating = True
End Sub

This macro allows you to select the destination workbook.

 

[Sarthakjaiswal44]

Hello

Thanks for the quick reply, But i think you did not get my query.

i want to copy the content of the parent workbook into the new workbook. your code just opens the parent workbook again.

 

[mumps]

Manually open the parent workbook and place the macro in a standard module. Run the macro. Select the destination workbook in the pop-up. The parent workbook must be opened manually.

 

[Sarthakjaiswal44]

Not helpful again.

let me describe it.

I have one workbook by the name sample_data.xlsm, it has a data in "Sheet1" from A1:J14. This is the sample data that needs to be copied whenever I make a report to send it to my boss.
NOTE: the sample data remains the same every time.Also this worksheet will contain the VBA code as I would like to store macros in this workbook.

Now I already have a VBA Macros ready. the code includes.
1) creating a pivot table
2) formatting the data.

3) finally I want to copy the sample data also and hence code is required for this.



Now I have another workbook, with the name week35.xlsx where the code has to work.

Hope you understood now, just help me with the code to copy+paste the data in my previous code.
I cannot go with the code you gave me above.

 

[mumps]

I tested the macro I suggested and it does exactly what you requested. The macro must be placed in a regular module not the worksheet module in the sample_data.xlsm workbook. In your original post you said

I cannot set the destination workbook name beforehand

That is why the macro opens the file picker dialogue window. This allows you to choose the destination file regardless of its name.

 

[Sarthakjaiswal44]

Hii ,

Plz visit the link to see my excel sheets.
https://drive.google.com/file/d/1R9SgDznAJk1u9pkCfOF9S1j5r8NLzP61/view?usp=sharing

excel on the left side has the sample data that has to be copied to excel on the right side.
the only condition is destination will have different sheets and thus destination sheet name cannot be saved in the code. Also this code has to be added to remaining code and hence your above method of choosing and opening of sheet has to be avoided.

 

[mumps]

I think there is some confusion as to whether the destination workbook or the destination worksheet cannot be saved in the code. Please clarify in detail which one cannot be saved in the code. I cannot suggest a code if it has to be added to existing code. I will need to see the existing code and you need to explain where in that code the new code has to be inserted. Instead of posting a link to screenshots, it would be easier to help and test possible solutions if I could work with your actual files which include any macros you are currently using. Perhaps you could upload a copy of your source and destination files to drive.google.com and post the link. If the workbooks contains confidential information, you could replace it with generic data. Please be very detailed an specific in your explanation of what you want to do.

 

[TotallyConfused]

Hello Mumps

I’ve been following this thread with great interest because before long, I may also be faced with the problem of copying data from one workbook to another. I love the code you supplied to open a selection window (post #2 ) where the user can select a destination file, because I think that will be a solution I’ll need to have available. I’m keeping a copy of that code. Thanks for your help in advance.

Now a question. In post # 5, Sarthakjaiswal44 says, “Now I have another workbook, with the name week35.xlsx where the code has to work.” I’m assuming that file will be one of the destination workbooks. If this is the case, and the code has to work in it, shouldn’t that file have the extension of .XLSM and not .XLSX?

Keep up the good work. I think you are doing great.

TotallyConfused

 

[mumps]

@TotallyConfused: Thank you for the kind words and I'm glad you found my code useful. When you say

shouldn’t that file have the extension of .XLSM and not .XLSX?

you are perfectly correct. Any file that contains a macro should be saved as a macro-enabled file which will change the extension to "xlsm".