Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

581 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