Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Worksheets (VBA/Excel2K)

    Created a macro that adds additional worksheets if the current worksheet has more than 5000 lines. Need to know what the syntax should be of a dynamic array that determines the number of worksheets and takes the header row off of sheet1 and copies it across the array.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Multiple Worksheets (VBA/Excel2K)

    ActiveWorkbook.Worksheets is the collection of all worksheets in the current workbook. The number of worksheets is ActiveWorkbook.Worksheets.Count.

    Can you use this code as a starting point?

    Dim i As Integer
    ActiveWorkbook.Worksheets("Blad1").Rows(1).Copy
    For i = 1 To ActiveWorkbook.Worksheets.Count
    If Not (ActiveWorkbook.Worksheets(i).Name = "Sheet1") Then
    ActiveWorkbook.Worksheets(i).Paste ActiveWorkbook.Worksheets(i).Rows(1)
    End If
    Next i
    Application.CutCopyMode = False

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Worksheets (VBA/Excel2K)

    Worked beautifully! Thanks!

Posting Permissions

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