Solved

Excel 2007 - copying from one workbook to another

Posted on 2010-11-28
12
477 Views
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
0
Comment
Question by:Morya1
12 Comments
 
LVL 30

Expert Comment

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

Sid
0
 

Author Comment

by:Morya1
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"
0
 
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.
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:Morya1
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
OR
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
0
 
LVL 37

Accepted Solution

by:
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
Next

End Sub

Open in new window

0
 

Author Comment

by:Morya1
ID: 34294932
Sorry for the delay.

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

Expert Comment

by:TommySzalapski
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.
0
 

Author Comment

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

Author Closing Comment

by:Morya1
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.
0
 
LVL 37

Expert Comment

by:TommySzalapski
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.
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

770 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