+ Reply to Thread
Results 1 to 2 of 2

Copy Cells from differnet workbooks to a sigle one

  1. #1
    Registered User
    Join Date
    10-11-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2

    Copy Cells from differnet workbooks to a sigle one

    Hi All,

    I am new to macro, I would like to copy D1 and B9 cells from all the work sheet from a particular location in the SharePoint to a master sheet(destination sheet) with out opening the Source sheets. . The source sheets are of different name, but in the same location and there will be approximately 25 to 30 sheets.

    If you open the Master sheet. I want the D1 data from source sheet (1 October 2013.xlsx) in A2 and B9 data in B2 and then the source sheet (2 October 2013.xlsx) in A3 and B9 data in B3 and so on. I only want to open the master file and do not want to open the source files.

  2. #2
    Registered User
    Join Date
    10-11-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Copy Cells from differnet workbooks to a sigle one

    I have created the below code. but this have the below issues.

    1. It is giving the error. "path not found"(the folder is in portal "//portal/Serve/RS/SOL Call Handling/2013/September 2013")

    2. if i change the path to a normal folder, the code will work, but it will copy the formula in B9 from the source sheet and not the value in B9.

    Could some one let me know how to resolve this.


    Sub copyAllWbInFolderToActiveSheet()
    Dim fileName, ws As Worksheet
    Dim rng As Range
    Dim destWB As Workbook
    Dim pPath As String
    pPath = "http://portal/Serve/RS/SOL Call Handling/2013/September 2013" ' <<< to be changed
    Set destWB = ActiveWorkbook
    LR = 2
    With CreateObject("scripting.filesystemobject")
    For Each fileName In .GetFolder(pPath).Files
    With Workbooks.Open(fileName)
    .Sheets(1).Range("D1").Copy destWB.Sheets(1).Cells(LR, 1)
    .Sheets(1).Range("B9").Copy destWB.Sheets(1).Cells(LR, 2)
    .Close True
    End With
    LR = LR + 1
    Next
    End With
    End Sub
    Last edited by anilkp3183; 10-11-2013 at 11:07 AM.

+ 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. Copy Data from 100 workbooks with cells into a master workbooks
    By roy__lam in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2013, 02:11 PM
  2. Columns with differnet width cells
    By excelbobabc in forum Excel General
    Replies: 1
    Last Post: 08-13-2009, 10:17 PM
  3. Splitting A number into four differnet cells
    By Everquest in forum Excel General
    Replies: 8
    Last Post: 04-25-2007, 01:59 AM
  4. Counting cells that meet 2 differnet criteria
    By USChad in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-07-2005, 03:05 PM
  5. Replies: 2
    Last Post: 04-25-2005, 07:06 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