Results 1 to 6 of 6

Running code for three sets of worksheets

Threaded View

  1. #1
    Registered User
    Join Date
    07-13-2013
    Location
    syracuse, ny
    MS-Off Ver
    Excel 2003
    Posts
    9

    Exclamation Running code for three sets of worksheets

    Hi, I have a vba problem that probably has a very easy answer.
    I have the code below which formats a table so that I can make a pivot table out of it. What I need to do now is to apply this code to 3 different sets of worksheets. The first set (below) takes data from "M) Avg Hrs- Month" and formats the values into a table labeled "M) Data for PT." I know need this code to run for "A) Avg Hrs- Month"/ "A) Data for PT" and "N) Avg Hrs- Month"/"A) Data for PT." I canot just copy and paste the code and change the names of the worksheets it uses because the variable names are the same.

    Thanks in advance everyone!! I am a BIG vba novice so any help is appreciated!

    A sample workbook is attached!


    
    Option Explicit
    Sub ReorgData()
    
    With Excel.Application
            .ScreenUpdating = False
            .Calculation = Excel.xlManual
            .EnableEvents = False
    End With
    
    
    'find active range
    Sheets("M) Avg Hrs- Month").Select
    Columns("A:A").Select
        Selection.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
        ActiveCell.Offset(-1, 0).Select
        Cells.FindNext(After:=ActiveCell).Activate
        ActiveCell.Offset(0, -1).Select
        Range(Selection, Selection.End(xlUp)).Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Selection.Copy
    
    Dim a As Variant, b As Variant
    Dim c As Long, i As Long, ii As Long
    With Sheets("M) Avg Hrs- Month")
      a = Selection
      ReDim b(1 To (UBound(a, 1) * (UBound(a, 2) - 3)), 1 To 5)
    End With
    For c = 4 To UBound(a, 2)
      For i = 2 To UBound(a, 1)
        ii = ii + 1
        b(ii, 1) = a(i, 1)
        b(ii, 2) = a(i, 2)
        b(ii, 3) = a(i, 3)
        b(ii, 4) = a(1, c)
        b(ii, 5) = a(i, c)
      Next i
    Next c
    With Sheets("M) Data for PT")
      .UsedRange.ClearContents
      With .Cells(1, 1).Resize(, 5)
        .Value = [{"Resource Name","Team","Department","Month","Hours"}]
        .Font.Bold = True
      End With
      .Cells(2, 1).Resize(UBound(b, 1), UBound(b, 2)) = b
      .Columns.AutoFit
      .Activate
    End With
    
    'delete extra
    Sheets("M) Data for PT").Select
    Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Selection.Delete
        Range("A1").Select
    
        
    'Paste Values
    Sheets("M) Data for PT").Select
        Columns("E:E").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A1").Select
        Columns("E:E").Select
        Selection.NumberFormat = "0.0"
        Range("A1").Select
        ActiveWorkbook.Save
    
    With Excel.Application
        .ScreenUpdating = True
        .Calculation = Excel.xlAutomatic
        .EnableEvents = True
    End With
        
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. 2 different sets of code in 1 tab
    By fionaby in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-10-2012, 05:45 AM
  2. How to match two sets of data between two worksheets
    By humptydumpty in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2010, 04:48 AM
  3. Transferring Data Sets Between Worksheets
    By eichstadt28 in forum Excel General
    Replies: 3
    Last Post: 04-14-2009, 12:16 PM
  4. Error code when I run 2 sets of code.
    By richard11153 in forum Excel General
    Replies: 1
    Last Post: 11-28-2008, 02:33 AM
  5. Hiding and Unhiding two sets of worksheets
    By Glio in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-14-2008, 03:09 PM

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