+ Reply to Thread
Results 1 to 10 of 10

Copy Paste revision

  1. #1
    Registered User
    Join Date
    12-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    99

    Copy Paste revision

    Igot this VBA code on this forum. It works perfectly for my purpose but I justneed some help for a small revision.

    Insteadof selecting or looking for the file, I want to have a code to select thespecific drive and file. The file to be imported is from drive Z:\SHARED\DATA\LOCATION.xlsb

    (i think this line should be revised, "vFile = Application.GetOpenFilename("Locator Files Only(*.xlsb)," & "*.xl", 1, "Select Locator File", "open", False)"

    Hereis the code: (also please check if the sequence is correct)

    SubLOCATOR()
    Application.ScreenUpdating = False
    Dim vFiles As Variant
    Dim wbCopyTo As Workbook
    Dim wsCopyTo As Worksheet
    Dim wbCopyFrom As Workbook
    Dim wsCopyFrom As Worksheet

    Set wbCopyTo = ActiveWorkbook
    Set wsCopyTo = ActiveSheet
    lastrow =Range("A7").End(xlDown).Row

    vFile = Application.GetOpenFilename("LocatorFiles Only(*.xlsb)," & "*.xl", 1, "Select LocatorFile", "open", False)

    If TypeName(vFile) = "Boolean"Then
    Exit Sub

    Else
    Set wbCopyFrom = Workbooks.Open(vFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets("DATAENTRY")
    End If
    '--------------------------------------------------------------
    'Clear contents of Target worksheet
    wsCopyTo.Range("D7:Q" &lastrow).ClearContents
    'Copy Range
    wsCopyFrom.Range("B7:O" &lastrow).Copy
    wsCopyTo.Range("D7").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone,SkipBlanks:=False, Transpose:=False
    Application.DisplayAlerts = False
    'Close file that was opened
    wbCopyFrom.Close savechanges:=False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    End Sub


    Thank you in advance.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy Paste revision

    Hi Gemini,

    See if this works:

    Please Login or Register  to view this content.
    BTW - you need to wrap your code in code tags (just highlight the code and click on the #)
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    12-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: Copy Paste revision

    Thanks xladept. It works perfectly.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy Paste revision

    You're welcome!

  5. #5
    Registered User
    Join Date
    12-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: Copy Paste revision

    Hi XLadept,

    Is it possible that the imported data be sorted before pasting? If so, can you revised the code to include sorting the data. Sorting sequence key is column "F" and sorted from column "D" to column "R". Thank you.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy Paste revision

    We're copying from B-O and pasting from D-Q yet you want to sort from D-R on the from sheet????

    I need clarification here - it seems that the proper sort would be from D-Q on the copy to sheet???

  7. #7
    Registered User
    Join Date
    12-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: Copy Paste revision

    Sorry xladept, my mistake. You are correct, sorted is from D-Q. Also, is it possible to remove duplicate on colum "F" (if any)?. Thanks.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy Paste revision

    Hi Gemini,

    Try this - let me know of any issues:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: Copy Paste revision

    Hi xlAdept,

    Based on this original VBA code below, sometimes i get an error message 400. It open the file and freeze but does not copy and an error 400 is displayed. Sometimes its working fine, but lately, it occurs more often. I am using Excel 2010. Kindly check if something has to be added on the code? Thanks.

    Here is the original VBA code:
    Sub LOCATOR()
    Application.ScreenUpdating = False

    Dim wbCopyTo As Workbook
    Dim wsCopyTo As Worksheet
    Dim wbCopyFrom As Workbook
    Dim wsCopyFrom As Worksheet

    Set wbCopyTo = ActiveWorkbook
    Set wsCopyTo = ActiveSheet
    lastrow = Range("A7").End(xlDown).row

    Set wbCopyFrom = Workbooks.Open("Z:\SHARED\DATA\LOCATION.xlsb")
    Set wsCopyFrom = wbCopyFrom.Worksheets("DATAENTRY")

    '--------------------------------------------------------------
    'Clear contents of Target worksheet
    wsCopyTo.Range("D7:Q" & lastrow).ClearContents
    'Copy Range
    wsCopyFrom.Range("B7:O" & lastrow).Copy
    wsCopyTo.Range("D7").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.DisplayAlerts = False
    'Close file that was opened
    wbCopyFrom.Close savechanges:=False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    End Sub

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy Paste revision

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automate copy and paste and copy and paste back to excel
    By Bmw318be in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2013, 05:42 AM
  2. Replies: 1
    Last Post: 01-16-2013, 05:36 AM
  3. 2 Macro's: only vertical copy/paste action and copy-paste 14 columns to the right.
    By vdongen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2010, 10:34 AM
  4. Revision to Formula/Macro to Check Column and Copy Row to Other Sheet
    By alachape in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2008, 09:03 AM
  5. Replies: 1
    Last Post: 01-04-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1