Macro to: open workbook, copy multiple values, paste them to another workbook.

Hey, I need a macro which would open a workbook, copy values which are stored in many different (but always the same) cells and paste it a template also to many different, but always the same cells. Unfortunately it is not possible to move those cells in any of those files to simplify this process. I have a tiny bit of code, but I have no idea how to make it to copy and paste many cells t once. Mine copies either a cell or a range of cells which are grouped together. I'm not adding any example file caus it could work the same way on any 2 workbooks. If any will be needed - I can provide some.

Please Login or Register  to view this content.

If there is no relative positioning for all the copy/paste locations, then you'll need to list each pair of cells/ranges. That said, this set-up should make it easier to do so...

Please Login or Register  to view this content.

Hey, unfortunatelly it's not working. It stops as on attached screen.Attachment 685168

Sorry, can't open your attachment.

Oh, strange. But it stops here: „ With ThisWorkbook.Worksheets("SelectFile")”

If the error is "Subcript out of range" then that worksheet was not found in the workbook running the code. Possibly a spelling mistake? or added space?

OK, I've tested it with the correct name of the sheet now, but it didn't execute copying. Macro went through, original file was opened and closed, but there are no values in the destination file. Cells B11, B13, C12, C14 are empty after the macro finished. In the file I'm trying to copy from those values are calculated formulas. Can it have influence on this?


Sub Get_Data_From_File()
Dim FileToOpen As String
Dim OpenWS As Worksheet


FileToOpen = Application.GetOpenFilename(Title:="Select calculated PPR and import data to MAGNA OS template", _
FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen = "False" Then Exit Sub

Application.ScreenUpdating = False

Set OpenWS = Workbooks.Open(Filename:=FileToOpen, ReadOnly:=True).Worksheets(1)
With ThisWorkbook.Worksheets("SelectFile")
.Range("V24") = OpenWS.Range("B11")
.Range("V23") = OpenWS.Range("B13")
.Range("V22") = OpenWS.Range("C12")
.Range("V29") = OpenWS.Range("C14")
' .Range("A10") = OpenWS.Range("V19")
' ...List all the data transfers needed

End With
OpenWS.Parent.Close False

Application.ScreenUpdating = True
End Sub

Is the data to be collected on worksheets(1)? (Worksheets(1) is the left-most tab)

Yes, it is. Well...the name of this worksheet is different.

What was the error?

There was no. It just did nothing apart from opening and closing workbook.

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Ok, there you go. Two files: "copy_from_here" - I need to extract data from this file, from tab "calculations" and paste it to workbook ""copy_to_this_file". There are tabs "_before" and"after" to visualize what needs to be done. Hope this is clear. Of course as said before this macro has to open and close file "copy_from_here" without saving any changes and paste data as values to the second file.

This worked on your sample files...

Please Login or Register  to view this content.

This is very strange. It is not working for me. Please see screenshots attached. I hope it will be visible this time. Error is the same as previously when I've tried to attach screenshots.

The code as written needs to be run from the destination workbook. If not, you'll need to replace 'Thisworkbook' with the full name of the workbook containing the code.

It was run from the destination workbook.

Use this file...

I don' get it, but it worked :o
Thanks so much. Now will try to apply to my real files.

Good luck!