Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel 2007 - copying from one workbook to another

Posted on 2010-11-28
12
483 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

856 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