Create Workbook and move data

  • I need to take every row (2 through...) from a downloaded spreadsheet and place them in their own spreadsheets. The next day a new spreadsheet will be downloaded and it's rows will need to be added to the previous spreadsheets that were created the first time. This process needs to be done every day.

    Can we create a macro or write VBA code to do it? I'm stuck on how to do loops when creating new workbooks or worksheets... every time i run the program.

    Here is an example of the downloaded spreadsheet... and a second example representing the following day's downloaded spreadsheet.

  • The column named Number defines how many spreadsheets need to be generated. There are 19 rows in the sample, so that would generate 19 spreadsheets. I've attached an example of what one of these spreadsheets (one of 19) would look like if it had been updated six times.

    This macro works but only once. Then, I have to delete what I have generated and start over. It is only generating one spreadsheet when it will needs to generate 19 (for this group). Others may have as many as 200.

    Another dilema is that my macro only works when all six sample-data sheets are open. In reality, I will only get one of these per day, and I need to add it's data to the 19 spreadsheets.

    I hope this example helps.


    P.S. the naming convention could be based on the Number

  • I was affraid you would ask for that. It's long and tedious... but here goes nothing. it's referencing six different spreadsheets.

    I couldn't figure out how to get a loop to work with the names of the downloaded spreadsheets. The real names look like this:

    The date of the download is in there.

  • It's not working. There seems to be an error on the line I referenced. I'm assuming it works on your computer. Why would it be different over here?

  • Depends what the error is but I would have thougth the path and name do not contain the correct information.

    add the following before the line of code to see the contents
    Msgbox Path & Name


  • Ahhhhh, yes. It says

    C:\Documents and Settings\Owner\Desktop\DownloadedSpreadsheet.xls

    What part of the code do I put that in? Or should I just move the location of the spreadsheet?

  • Put the workbook with the code into the same folder as the download and output workbooks if you want to continue ti use Thisworkbook.path
    otherwise change Thisworkbook.Path to the path you actual want to use.


  • Oh My God!!! This is amazing!!!!! This has been unbelievablly helpful. I've learned so much - and my boss will be thrilled. THANKS!!!! :gift: :thanx: :) I owe you one.

  • LBound and UBound from Excel's GetOpenFilename method

    I must ask for a little more assistance on this project.

    I'm adding the user interface parts to the previous code and the perfect example from the book, Excel Bible (pg 1072), won't work. There is something funny about LBound and UBound. It's expecting an array... I'm not sure what to do.

    I've erased that section of the code... then it tries to run but gets caught on the line defining the FileName.

    Can anyone tell me what to do?

    Here is the code:

  • Yes. And when I do that I get a compile error: ByRef argument mismatch... as it refers to the second to last line when I use filename again.

    I started another string... and recieved the same advice... but the code for creating new workbooks doesn't seem to work with the dialogue box code.

    So this code was added to the previous advice

    '   Display full path and name of the files
        For i = LBound(FileName) To UBound(FileName)
            Msg = Msg & FileName(i) & vbCrLf
            Workbooks.Open FileName:=FileName(i)
        Next i
    End Sub

    and then I tried to combine it with our previous work of art like this

    it probablly need to be combined with the GetOutput sub not the EnterFilename Sub... but I'm confused now.

  • Either change the variable type in the MyLoad routine.
    I haven't got the code here but something like

    Sub MyLoad(Path as String , File as variant)

    Or convert the argument that you pass it, something like
    MyLoad ThisWorkbook.Path & "\", Cstr(FileName(i))


  • It opens the six downloaded files but doesn't create the 19 new ones.

    And then it hangs on this part of the code

    MyLoad ThisWorkbook.Path & "\", CStr(FileName(i))

    So I add the other suggestion you give and there are no compile errors but the program does not perform. :o(

    I keep trying different arrangements.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!