Solved

FInd and Paste

Posted on 2011-09-25
2
143 Views
Last Modified: 2012-06-21
Hello,

Trying to build a macro in workbook "Overall" thta will do the following.

1. Open workbook "Data1", which is located in C:/user1/Data.xlsx
2. Copy the data in sheet "In-Outbound" from the range of A2:E to where the value of CALL is located in Column A For example if the word CALL is in A 18, then copy A2:E18.
3. Paste that value in workbook Overall.Sheet "In-Outbound" on the next empty row after the cell that contains Outbound.  For example if the work Outbound is in cell A1, then paste in cell A2.
0
Comment
Question by:sandramac
2 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 36596995
why you don't record a macro that is doing import and see what you get?
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 36597389
>> Open workbook "Data1", which is located in C:/user1/Data.xlsx

I'm assuming the name of the workbook is Data.xlsx and not "Data1", per the below line in the code (which you can change):

fName = "c:\users1\data.xlsx" 'change your filename here - is it Data.xlsx or Data1.xlsx????

Both the source workbook, "Data.xlsx" and destination workbook "Overall.xls" have a sheet tab named, "In-Outbound", based on your description

>>Paste that value in workbook Overall.Sheet "In-Outbound" on the next empty row after the cell that contains Outbound.  For example if the work Outbound is in cell A1, then paste in cell A2.

Taking you literally - "next empty row" as opposed to "next row following" the word Outbound in column A.  So, if you import more than once, the NEXT empty row at the bottom of the dataset, following Outbound, would get the data.  make sense :)

Here's the code:

Sub overall()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim srcWkb As Workbook
Dim srcWks As Worksheet
Dim srcRng As Range
Dim fName As String
Dim fSrcRange As Range, fRange As Range

    'fName = "c:\users1\data.xlsx" 'change your filename here - is it Data.xlsx or Data1.xlsx????
    fName = ActiveWorkbook.Path & "\data.xlsx"
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("In-Outbound")
    
    'find Outbound in the destination workbook (ThisWorkbook)
    Set fRange = wks.Range("A:A").Find(what:="Outbound", LookIn:=xlFormulas, lookat:=xlWhole) 'searches for whole word "Outbound"
    
    If fRange Is Nothing Then
        MsgBox "Could not find 'OutBound' in this workbook's sheet Overall.Sheet, Column A - please check spelling, etc.", vbCritical, "Aborting..."
    Else
                
        'Open the Source workbook
        On Error GoTo errHandler
        Set srcWkb = Workbooks.Open(Filename:=fName, UpdateLinks:=2, ReadOnly:=True)
        On Error GoTo 0
        
        Set srcWks = srcWkb.Sheets("In-Outbound")
        
        'find CALL in the source workbook
        Set fSrcRange = srcWks.Range("A:A").Find(what:="CALL", LookIn:=xlFormulas, lookat:=xlWhole) 'looks for whole word "CALL"
        
        If fSrcRange Is Nothing Then
            MsgBox "Could not find 'CALL' in this workbook's sheet Overall.Sheet, Column A - please check spelling, etc.", vbCritical, "Aborting..."
        Else
        
            'copy A2:E lastrow indicated by CALL in column A
            Set srcRng = srcWks.Range("A2:E" & fSrcRange.Row)
            
            'paste to column A lastrow indicated by Outbound, the next empty row following "Outbound"
                    
            Set rng = wks.Range("A" & fRange.Row).End(xlDown)
            If rng.Row = wks.Rows.Count Then 'hit bottom, so "repair range address
                Set rng = wks.Range("A" & fRange.Row).Offset(1, 0) 'use the row following Outbound, instead, as it is really the next empty row
            Else
                Set rng = rng.Offset(1, 0) 'the row following the End(xlDown) is the empty row, in this case
            End If
            
            'now do the actual copy/paste
            srcRng.Copy
            rng.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
        End If
        srcWkb.Close savechanges:=False 'clean up
    End If
    
cleanUp:

    Exit Sub
    
errHandler:
    MsgBox "for some reason, the file " & fName & " was unable to open, successfully", vbCritical, "Aborting!"
    
End Sub

Open in new window


There's a bit of error checking - letting you know it didn't find Outbound, or CALL, and also that it couldn't open the file data.xlsx (e.g., file doesn't exist).  If you get an error on the find on Outbound or the find on CALL, try to ensure there are no extra spaces or special cells in the text where Outbound or CALL exist.  Let me know if you have any issues.

See attached workbooks, and test file demonstrating everything works.

Enjoy!

Dave

 
Overall-r1.xlsm
Data.xlsx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

789 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