Results 1 to 11 of 11

Excel 2003 vs. 2007 VBA Issue

Threaded View

  1. #1
    Registered User
    Join Date
    11-18-2009
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    5

    Unhappy Excel 2003 vs. 2007 VBA Issue

    I have written the following code to take lines of data off a "Master" tab and create separate "forms" that are prepopulated from the original data. It should only create/pre-fill as many forms as there are lines of data in the Master tab. This code works beautifully on my Excel 2007 at home, but I need it to work on Excel 2003 at the office. No luck.

    Please correct the code so that it'll work on both!

    Thanks so much!!

    Sub Copier2()
        Dim x As Range
        Dim y As Integer
        Dim z As Integer
        Dim SheetName As String
            
        Dim Title As String
        Dim Observation As String
        Dim Risk As String
        Dim Department As String
        Dim Description As String
        Dim CorrectiveAction As String
           
        Set x = Workbooks.Item(1).Worksheets.Item("Master").Range("F2")
        y = 3
        z = 2
    
        For numtimes = 1 To x
            'Loop by using x as the index number to make x number copies.
            'Replace "Sheet4" with the name of the sheet to be copied.
                
            ActiveWorkbook.Sheets("Report").Copy _
            After:=ActiveWorkbook.Sheets("Report")
            
            Title = "A" & y
            Range("Title").Select
            ActiveCell.FormulaR1C1 = Workbooks.Item(1).Worksheets.Item("Master").Range(Title)
            
            Observation = "F" & y
            Range("Observation").Select
            ActiveCell.FormulaR1C1 = Workbooks.Item(1).Worksheets.Item("Master").Range(Observation)
            
            Risk = "K" & y
            Range("Risk").Select
            ActiveCell.FormulaR1C1 = Workbooks.Item(1).Worksheets.Item("Master").Range(Risk)
            
            Department = "D" & y
            Range("Department").Select
            ActiveCell.FormulaR1C1 = Workbooks.Item(1).Worksheets.Item("Master").Range(Department)
            
            Description = "H" & y
            Range("FindingDescription").Select
            ActiveCell.FormulaR1C1 = Workbooks.Item(1).Worksheets.Item("Master").Range(Description)
            
            CorrectiveAction = "M" & y
            Range("CorrectiveAction").Select
            ActiveCell.FormulaR1C1 = Workbooks.Item(1).Worksheets.Item("Master").Range(CorrectiveAction)
            
            y = y + 1
            z = z + 1
                    
        Next
        End Sub
    Last edited by astronautika; 11-18-2009 at 04:02 PM.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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