Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Want to rename columns, delete unwanted columns, and reconcile reports

    Greetings,

    Given the attach sample workbook with order and receiving report, I would like to:

    1. Rename column names that are labeled differently in receiving report to match those in order report

    2. Delete the remaining columns

    I'll then use vlookup, macro, or formulas to reconcile the two reports.

    What would be the best tool (Access or Excel) to automate this process?

    TIA,

    Regards,
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Since you are in excel, I think excel. How about some code like this (test on a copy)
    Code:
    Option Explicit
    Sub RenDelCols()
      Dim vCols As Variant
      Dim vNames As Variant
      Dim iCols As Integer
      Dim iCol As Integer
      Dim wks As Worksheet
      Dim i As Integer
      
      'define the worksheet
      Set wks = Worksheets("Receiving")
      'Cols in Receiving to be renamed
      vCols = Array(1, 12, 14, 17, 24, 25) 'Cols A,L,N,Q,X,Y
      'Names from Order to rename them to
      vNames = Array("Order Date", "Order Number", "Order Type", "Supplier", "Total", "Account Code")
      
      With wks
        iCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For iCol = iCols To 1 Step -1
          i = 0
          'check if col number is one to change
          On Error Resume Next
          i = Application.WorksheetFunction.Match(iCol, vCols, 0)
          On Error GoTo 0
          If i = 0 Then
            'column is not in list, delete it
            .Columns(iCol).EntireColumn.Delete
          Else
            'col is in list, rename it
            .Cells(1, iCol).Value = vNames(i - 1)
          End If
        Next
      End With
    End Sub
    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Steve,

    It worked nicely for my second report (Receiving Report): renamed needed columns, and deleted unwanted columns.
    However, I would also like to delete unwanted columns from my first report (Order report). With the exception of the following:

    Order Number (A]
    Order Date (E)
    Order Type (F)
    Blanket Order (H)
    Supplier (L)
    Total (W)
    Account Code (X)

    Regards,

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You should be able to adapt the code above. Change the worksheet name, change columns numbers in the array and delete the lines of code that rename the columns headings.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve, thanks and I have the needed columns in both my reports (order and receiving) that are in the same workbook. What would be the best way to load them into one report for reconciliation purposes?

    Regards,

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can create a new sheet and copy the columns into that one or copy one set into the other.

    If you are unsure of the code, I would recommend do the task with the macro recorder to see the objects.

    Steve

Posting Permissions

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