Excel 2007 - copying from one workbook to another

Posted on 2010-11-28
Medium Priority
Last Modified: 2012-05-10
I have a spreadsheet: Read-From-Outlook--4-.xlsm and I need to paste the values from its rows into a specific tab in another workbook. visa.xlsm and the sheet the data needs to go to is: Wire-Staging-FBME.

So, what I'd like is that when I click a button in Read-From-Outlook--4-.xlsm data is transferred to sheet Wire-Staging-FBME if the value from Read-From-Outlook--4-.xlsm in the Currency column = EUR.

Please see attached flow chart for details.   one sheet to another
Question by:Morya1
LVL 30

Expert Comment

ID: 34228904
Perhaps, a sample of your workbook might expedite what you want as it might help us in further understand your requirement?


Author Comment

ID: 34230164
Sure if you really need it. It's nothing more than I have indicated above. The bottom row of column headers represents the Wire-Staging-FBME sheet of visa.xlsm and the top image represents the sheet data is being imported from.

Ignore the statement at the bottom of the image: "bottom is EP Transfers sheet"
LVL 33

Expert Comment

by:Robberbaron (robr)
ID: 34238049
to move forward please do....
1/ open the source Outlook--4.xlsm workbook
2/ turn on the Macro recorder and do a single copy of the data manually.  (To a new sheet in the same workbook for ease of transfer)

3/ attach the Outlook--4.xlsm workbook, after stripping all non test data.  

4/ this way we can see what you are trying to do, have some test data and can tidyup / rewrite the generated macro.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Author Comment

ID: 34280298
Ok, finally, here are the two spreadsheets. As you will see with the spreadsheet receiving the data (Visa Earthport.xls) I removed a number of columns, but the column headings are still there, so the above attached image is still valid as to where I'm needing the data to be imported into, from Read-from-Outlook-copy.xls.

So, to recap, when the value in Col E of Visa Earthport.xls = EUR
the card number starts with the digit 4, then the copying of data from Read-from-Outlook-copy.xls needs to begin as described in the above image. Visa-Earthport.xls Read-From-Outlook-copy.xls
LVL 37

Accepted Solution

TommySzalapski earned 2000 total points
ID: 34286330
This code should get you close.
You moved some columns around so you may need to modify the ranges in the if block. (I could see nowhere for column B to go)
Also, you should make sure your two date columns are in the same format.
Sub Button1_Click()
Dim i As Integer, dstRow As Integer
Dim srcSheet As Worksheet, srcBook As Workbook
Dim dstSheet As Worksheet, dstBook As Workbook

Set srcBook = ThisWorkbook
Set srcSheet = srcBook.Sheets("Sheet2")

On Error Resume Next
Workbooks.Open ("C:\temp\Visa-Earthport.xls")
On Error GoTo 0
Set dstBook = Workbooks("Visa-Earthport.xls")
Set dstSheet = dstBook.Sheets("Earthport Wires")

dstRow = dstSheet.UsedRange.Row + dstSheet.UsedRange.Rows.Count

For i = srcSheet.UsedRange.Row To srcSheet.UsedRange.Row + srcSheet.UsedRange.Rows.Count
  If UCase(srcSheet.Range("E" & i).Value) = UCase("EUR") Or Left(srcSheet.Range("D" & i).Value, 1) = "4" Then
    dstSheet.Range("B" & dstRow).Value = Left(srcSheet.Range("A" & i).Value, 10)
    dstSheet.Range("C" & dstRow).Value = srcSheet.Range("C" & i).Value
    dstSheet.Range("D" & dstRow).Value = srcSheet.Range("D" & i).Value
    dstSheet.Range("E" & dstRow).Value = srcSheet.Range("E" & i).Value
    dstSheet.Range("H" & dstRow).Value = srcSheet.Range("F" & i).Value
    dstSheet.Range("G" & dstRow).Value = srcSheet.Range("G" & i).Value
    'dstSheet.Range("?" & dstRow).Value = srcSheet.Range("B" & i).Value
    dstRow = dstRow + 1
  End If

End Sub

Open in new window


Author Comment

ID: 34294932
Sorry for the delay.

Running the code I get an error. Please see attached. code error 1 code
LVL 37

Expert Comment

ID: 34295053
Either you renamed the file or it's open in another instance. Confirm the filename and make sure it is opening when the previous line is run.

Author Comment

ID: 34316200
Finally got around to it. Thanks Tommy. It works great!

Author Closing Comment

ID: 34316217
I am going to ask something more of this question like to not have it pull data already imported to Visa-Earthport/xls. Can you add that to the code so that it appends to what previously had been imported, Tommy? If so, I'll post another question. Let me know.
LVL 37

Expert Comment

ID: 34316235
Yeah, that can be done. You'll just have to mark them in some way and then check for the mark before copying. I would just use another column.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
I came across an unsolved Outlook issue and here is my solution.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question