+ Reply to Thread
Results 1 to 7 of 7

Macro to read multiple excel workshops, copy selected cells & consolidate work in workbook

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    australia
    MS-Off Ver
    Excel 2010
    Posts
    18

    Macro to read multiple excel workshops, copy selected cells & consolidate work in workbook

    Using the macro below, I was able to open files sequentially in a directory listing and select the target cell to be copied. However, the cell kept being overwritten and what I wanted was to have an output workbook which comprised a copy of all the entries. I couldn't figure out how to index my cell to a new position to avoid being overwritten.

    Any help you are able to provide would be invaluable?

    Dim Counter As Integer

    Opener = "d:\Projects\Futureact\Q4 Responses\openandcopy.xlsm" 'file i would like to contain all the output results
    FileDir = "d:\Projects\Futureact\Q4 Responses\" 'directory containing all the workbooks
    FileSearch = "*.xls"
    fileName = Dir(FileDir & FileSearch)
    Counter = 0
    Do While fileName <> ""
    Workbooks.Open fileName:=FileDir & fileName
    Range("B3:E3").Select 'unmerge cells
    Selection.MergeCells = False
    Range("B3").Select ' what to copy
    Selection.Copy
    Windows("OpenandCopy.xlsm").Activate ' name of the file to consolidate data

    Counter = Counter + 1 ' set it up so that each B3 cell is copied on a different row
    ' Range("Counter").Select ' don't know how to do it
    ActiveSheet.Paste
    Range("B4").Select

    Workbooks(fileName).Save 'move onto the next excel file to access data
    Workbooks(fileName).Close
    fileName = Dir()

    Loop
    End Sub

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Macro to read multiple excel workshops, copy selected cells & consolidate work in work

    Try something like this.

    ThisWorkbook refers to the workbook that contains the macro code. I assumed it was the same workbook you wanted to paste to.

    Change Sheet1 to the name of the destination sheet.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 08-09-2013 at 02:42 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    12-24-2012
    Location
    australia
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Macro to read multiple excel workshops, copy selected cells & consolidate work in work

    Thank you very much for your solution is works great. I was trying to build on the solution to allow me to copy about 10 rows of data and then go to next file. Again I ended up overwriting data in the first instance, I don't understand how End(xlup).Offset(1) works - i think the solution lies here. Any help you could be greatly appreciated. My code currently looks like:


    Sub openandcopy()

    Dim Counter As Long, FileDir As String, FileName As String

    FileDir = "D:\Projects\Futureact\Q4 Responses\" 'directory containing all the workbooks
    FileName = Dir(FileDir & "*.xls")

    Application.ScreenUpdating = False
    Do While FileName <> ""
    With Workbooks.Open(FileName:=FileDir & FileName)
    .ActiveSheet.Range("B3:E3").MergeCells = False 'unmerge cells
    .ActiveSheet.Range("B3").Copy _
    Destination:=ThisWorkbook.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1)
    .ActiveSheet.Range("A103:F113").Copy _
    Destination:=ThisWorkbook.Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
    .ActiveSheet.Range("J19:L29").Copy _
    Destination:=ThisWorkbook.Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
    'Next empty row in column B
    .Close SaveChanges:=True
    End With
    Counter = Counter + 1
    FileName = Dir
    Loop
    Application.ScreenUpdating = True

    MsgBox Counter & " files copied", vbInformation, "Copy Complete"

    End Sub

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Macro to read multiple excel workshops, copy selected cells & consolidate work in work

    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


    Try this...
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-24-2012
    Location
    australia
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Macro to read multiple excel workshops, copy selected cells & consolidate work in work

    Thanks - I seem to get an error #91, which Object variable or With Block variable not set on the following code line:

    NextRow = wsDest.Cells.Find("*", , , , 1, 2).Row + 1 'Next empty row on the destination worksheet

    No sure what I am meant to do - are you able to advise?

    I truly appreciate your help.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Macro to read multiple excel workshops, copy selected cells & consolidate work in work

    Try this...

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-24-2012
    Location
    australia
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Macro to read multiple excel workshops, copy selected cells & consolidate work in work

    A fantastic outcome - truly appreciated

+ 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. macro to copy cells to a new workbook doesn't work properly
    By dileva in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2013, 10:55 AM
  2. [SOLVED] Macro to read the multiple csv files and consolidate into single excel
    By parthmittal2007 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-03-2013, 03:40 AM
  3. Help! Macro to consolidate data from multiple workbook sheets!
    By Sainath Krishnan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2012, 08:18 AM
  4. copy selected range from multiple workbooks into multiple worksheets in one workbook
    By novak100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-13-2012, 05:52 AM
  5. Copy multiple cells from several work books to one workbook
    By Blur1973 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-20-2008, 08:20 PM

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