Excel 2007 - copying from one workbook to another

Posted on 2010-11-28
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 32

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.

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 500 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.


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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now