1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

copying cell data from multiple workbooks - Error 400

Discussion in 'Business Applications' started by polishdrumsticks, Dec 20, 2011.

Thread Status:
Not open for further replies.
  1. polishdrumsticks

    polishdrumsticks Thread Starter

    Joined:
    Dec 20, 2011
    Messages:
    1
    Hi all,

    I'm trying to create a master spreadsheet that based on a user input, reads every excel file in a certain folder and copies the values of cells b3, e3, e4 and d75 into sheet 2 on the current workbook.

    Basically files are stored in folders by month in the format Nov11, Dec11, Jan12 etc. The user picks month and year from drop down boxes (read as text). So if they pick November and 2011, I want the data from each file in the folder Nov11. The folders are all stored in the same directory as the master file.

    Below is my code...
    Keep getting error message 400 and don't know what it relates to!!.
    Don't know what I'm doing wrong...

    Please help...

    Code:
    Sub get_data()
    Dim Wb As Workbook
    Dim sFile As String
    Dim Cwb As Workbook
    Dim lrow As Long
    Dim getmonth As String
    Dim getyear As String
    Dim folder As String
    Dim monthyear As String
    getmonth = (Range("d7").Value)
    getyear = (Range("e7").Value)
    monthyear = Left(getmonth, 3) & Right(getyear, 2)
    folder = ThisWorkbook.path & monthyear & "\"
    Set Cwb = ThisWorkbook
    sFile = Dir(folder & "*.xls")
    Do While sFile <> ""
    If sFile <> Cwb.name Then
    On Error Resume Next
    Set Wb = Workbooks.Open(folder & sFile)
    lrow = Cwb.Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    lrow = lrow + 1
    Wb.Worksheets("Input").Range("E4").Copy
    Cwb.Worksheets("Sheet2").Range("A" & lrow + 3).PasteSpecial xlPasteValues
    Wb.Worksheets("Input").Range("E3").Copy
    Cwb.Worksheets("Sheet2").Range("B" & lrow + 3).PasteSpecial xlPasteValues
    Wb.Worksheets("Input").Range("B3").Copy
    Cwb.Worksheets("Sheet2").Range("C" & lrow + 3).PasteSpecial xlPasteValues
    Wb.Worksheets("Summary").Range("D75").Copy
    Cwb.Worksheets("Sheet2").Range("D" & lrow + 3).PasteSpecial xlPasteValues
    Wb.Close True
    End If
    sFile = Dir
    Loop
    Cwb.Worksheets("Sheet2").Range("A1").Select
    End Sub
    
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    welcome to the forum,

    I see you checked to avoid reading the current workbook.

    Have you tried stepping through the vba code?
    Only error 400 doesn't tell me anything, ahve you checked what the error code stands for/ Help file and error codes.
    I think going throught the code step by step will identify it.

    Debugging is a pain in the a** but sometimes that's the only way.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1032071

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice