+ Reply to Thread
Results 1 to 2 of 2

macro: copy data and create worbooks from a column

Hybrid View

  1. #1
    Registered User
    Join Date
    04-10-2008
    Posts
    2

    macro: copy data and create worbooks from a column

    Hello everybody!

    I`m sorry for my english, it`s not my native language.

    My problem is that one: I have an Excell database which contains Id`s in the first column and Names in the second. The aim is to create a new workbook per line which fiilename is "Id Name" and to copy the Id and the Name in the new workbook with reference to the original one (in case of changes in the original database) and to had it a structure that already exists.

    I`ve already found how to create all the new workbook and save them with a "good" name butI don`t know how to continue:

    Sub test()
        Dim newW As Workbook
        Application.ScreenUpdating = False
        For i = Sheets(1).Range("A50000").End(xlUp).Row To 1 Step -1
            FName = Sheets(1).Cells(i, 1).Value & " " & Sheets(1).Cells(i, 2).Value & ".xls"      
            Set newW = Workbooks.Add
            newW.SaveAs Filename:=FName
            Workbooks(FName).Close
        Next
        Application.ScreenUpdating = True
    End Sub
    
    'create a button
    Private Sub CommandButton1_Click()
    test
    End Sub


    Do you know if we can copy the structure everytime a workbook is created? and then insert the Id in A2 and the Name in B2?

    Thanks
    Last edited by bijnok; 04-10-2008 at 06:43 AM.

  2. #2
    Registered User
    Join Date
    04-10-2008
    Posts
    2
    I`ve tried this

    Sub test()
        Dim newW As Workbook
        Application.ScreenUpdating = False
        'Application.DisplayAlerts = False
        For i = Sheets(1).Range("A50000").End(xlUp).Row To 1 Step -1
            
            Dim A2 As String
            A2 = Sheets(1).Cells(i, 1).Value
            
            Dim B2 As String
            B2 = Sheets(1).Cells(i, 2).Value
         
            FName = Sheets(1).Cells(i, 1).Value & " " & Sheets(1).Cells(i, 2).Value & ".xls"
          
            Set newW = Workbooks.Add
            newW.SaveAs Filename:=FName
            
            With Workbooks(FName)
                
                Workbooks("structure.xls").Worksheets("timetable").Copy Before:=.Sheets(1)
                Sheets(1).Cells(2, 1).Value = A2
                Sheets(1).Cells(2, 2).Value = B2
                .Close
           End With
        Next
        
        
        'Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub
    but the loop ends after the first created workbook and the "display alerts" doesn`t work at all (I know it`s in quote there).
    Furthermore, I only copy the value in A2 and B2 of the new sheet but I`d like a formula ; for example "=............" so the cell will reference the main database and will automatically be updated with the main database...

    I need your help please....
    Last edited by bijnok; 04-14-2008 at 06:45 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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