Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Macro to autofill

    I hope i can describe this clearly. I have a workbook with 20 worksheets, each worksheet contains data about a particular salesman, all of the column headers are consistent.

    What i want to do is insert a new column as Col A and autofill the salesmans name down the Col A on each worksheet, so i can then combine all 20 tabs into one tab, so i know which sales belong to who. On each worksheet the individual's names are found in Cell D5 . The number of rows vary on each worksheet.

    I need a macro to complete the insertion of a new column A and the autofill of the salesmans names on each tab of the workbook, in one click. Is this doable?
    Thanks in advance for any assistance you can provide!

  2. #2
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    If you want to insert a new column at column A, wouldn't that now make the name that was in D5 now E5?

    Which column should we get the count for the last row?

    In the macro below, the LR count is coming from column A and I adjusted D5 to E5.

    Change those areas that meet your needs.

    Code:
    Sub AutoFill_2()
        Dim LR As Long
        Dim ws As Worksheet
        Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Columns("A:A").Insert
                LR = .Range("B" & .Rows.Count).End(xlUp).Row
                .Range("A2:A" & LR).Value = .Range("E5").Value
            End With
        Next ws
        Application.ScreenUpdating = True
    End Sub

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Jrb
    thank you, that worked great, I just realized that i have two tabs (positioned in front of the 20 tabs i need the code applied to), how do i modify your code... to not insert and autofill on the first two tabs... ?
    again, thanks for your help!!!

  4. #4
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    Maybe

    Code:
    Sub AutoFill_2()
        Dim LR As Long
        Dim ws As Worksheet
        Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            If Not ws.Name = "Sheet1" And Not ws.Name = "Sheet2" Then
                With ws
                    .Columns("A:A").Insert
                    LR = .Range("B" & .Rows.Count).End(xlUp).Row
                    .Range("A2:A" & LR).Value = .Range("E5").Value
                End With
            End If
        Next ws
        Application.ScreenUpdating = True
    End Sub

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Thumbs up

    This does exactly what I needed...thanks for your help!!!

  6. #6
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I have another request, in trying to clean up my tabs, I need to delete the full row, in any row below Row 9, where there is no data in Column G. Again leaving sheet 1 & 2 separated out from from the deletions. What code would you suggest?

  7. #7
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    This is a different topic from the title. Can you post a new thread for this separate query.

    In the meantime I can make up a macro for you.

Tags for this Thread

Posting Permissions

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