+ Reply to Thread
Results 1 to 8 of 8

[VBA] Open excel file and copy in last empy cell

  1. #1
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    162

    Question [VBA] Open excel file and copy in last empy cell

    Good day to all,

    I need your help in making for me very use full macro.
    macro has to open excel file with name from cell A1 in a path (expl C:\Users\ ) ..to made it short..i have a lot of files in a folder and now I want that when I run macro it opens file from a predefined path (C:\Users\) with name form cell A1 (example February2013).

    The second step is to select range A10:B50 in a opened file (February2013), and copy the contest in the last empty cell in column A in the first file (from when I run the macro). (Just to select range A10:B50, copy and paste it in last empty cell in column A of the first file).

    Thanks for help,

    forfiet

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: [VBA] Open excel file and copy in last empy cell

    Maybe:

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    162

    Re: [VBA] Open excel file and copy in last empy cell

    Yes it is Woking ;-)
    another request... is possible to paste only values (special paste)?

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: [VBA] Open excel file and copy in last empy cell

    Try this:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    162

    Re: [VBA] Open excel file and copy in last empy cell

    I dont know why but is not working..

    The first problem is that not opens requested file..is possible do define the extension .xls

    The second it gives error 424...

    Pleas help

  6. #6
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    162

    Re: [VBA] Open excel file and copy in last empy cell

    Sub Copy()

    Workbooks.Open ("C:\Users\" & Sheets("List1").Range("N2").Value & ".xlsm")


    Range("A10:B50").copy
    Windows("OPS.xlsm").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


    End Sub

    These code is working..but I want that it select last empty cell in column a and then special paste - only values

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: [VBA] Open excel file and copy in last empy cell

    You should have a sheet designation, change Sheet1 in the code to the Sheet name on Workbook "OPS.xlsm".

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    162

    Re: [VBA] Open excel file and copy in last empy cell

    Thanks for help ...
    Find bellow my finally working code:


    Sub Workbook_BeforeClose()

    Workbooks.Open ("C:\Users" & Sheets("List1").Range("N2").Value & ".xlsm")


    Range("A10:B50").copy
    Windows("OPS.xlsm").Activate
    Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Workbooks.Open ("C:\Users" & Sheets("List1").Range("N2").Value & ".xlsm")
    ActiveWorkbook.Close False

    End Sub
    is possible to made that if file with desired name is not find it shows "File with these name not found"?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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