Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Whoever answers this will be a god!!!!!!!!

  1. #11
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Right, I've been trying all the codes here but I can't get them to work. I am really, really rubbish with filepaths and don't know where to put them or anything. Thing is, the folder is called "Customer Services Debt", it's located in a subdirectory called "hbennett" which is the H Drive of our system. The summary file that I need this macro to run on is in the same folder as all these workbooks. So basically I will go into the summary workbook, press this button and it will magically (hopefully!) open all the other workbooks in the "Customer Services Debt" folder (all 60 of them) perform the copy on each workbook from a sheet called "Data" then paste each sheet's info back onto the summary sheet at 250 line increments, then close all the other 60 workbooks apart from the summary sheet.

    Phew! Out of breath now!!

    Janie
    xx

    P.S. Thanks you guys

  2. #12
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    I'm not quite sure I totally get what path is what but if I understand correctly, hbennet is mapped as drive H on your pc. And then Customer Services Debt is a folder in the root of drive H.
    With this in mind try the following adaptation to my macro (I've removed the "processed" malarky).

    Please note, while all the files you want to add together can be (and indeed have to be) all in the same directory - H:Customer Services Debt, the summary file called Statistics Collation.xls must be in a different directory of else the macro will try and call that file too to get data from.

    Sub getdata()

    Dim MyPath As String
    Dim MyFile As String
    Dim MyIncrement As Long

    'Set start row for pasting.
    MyIncrement = 4

    'Change the drive to whatever drive you're on
    ChDrive "H"


    'Define Path - change to whatever..
    MyPath = "H:Customer Services Debt"

    'Change directory to your directory
    ChDir (MyPath)

    'Now go looking for Excel files to process
    ChDir MyPath & ""
    MyFile = Dir("*.xls", vbNormal)

    Do While MyFile <> ""

    Workbooks.Open Filename:=MyFile

    'Do your stuff here (have shortened the code a bit - there were some unnecessary lines)...
    Sheets("Data").Activate
    Range("A2:J250").Copy
    Windows("Statistics Collation.xls").Activate
    Range("A" & MyIncrement).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    'Switch back to the open Excel file and close it, without saving
    Windows(MyFile).Activate
    ActiveWorkbook.Close SaveChanges:=0

    ' Call Dir again without arguments to return the next *.XLS file in
    ' the same directory.
    MyFile = Dir

    'Increment the value for pasting
    MyIncrement = MyIncrement + 250

    Loop
    End Sub

    Are we getting any closer???

    Rgds
    AJ


  3. #13
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OH MY GOD YOU ARE A BLINKIN' FLIPPIN' GOD AJ!!!!!!!!!!!!!!!!!!!!

    Janie

    xxxxxxxxxxx
    xxxxxxxxxxxxx
    xxxxxxxxxx
    xxxxxxxxxxxxxxxx
    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

    All for you, you absolute diamond!!!!

  4. #14
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Always a pleasure!
    BTW, I reckon jason's approach was a pretty nice one - less code than mine to do the same job.

  5. #15
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    It looked cool, but I have real trouble with this sort of thing and you explained it nicely, so thank you, thank you, thank you!!!!

    xxxxx

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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