Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Moving & Deleting Columns

    I have a spreadsheet that I download each month

    1) I would like to move the various columns to Col C & D based on a number in the column header for eg If I select 2 , then the Column headers containing 2 for eg 2/2014 and 2/2014(YTD) to be moved to Col C & D -This to be done for all sheets except the first sheet and the last sheet
    2) Once the columns have been moved then all the columns are to be deleted except Columns A-D on all sheets , except the first and last sheet

    I am looking for VBA code that will automate this for me

    Your assistance in this regard is most appreciated



    Attached please find sample data
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Instead of "I need a macro", why not try:

    1. Recording a Macro which does roughly what you want
    2. Trying adapting it to do exactly what you want
    3. Coming to this forum with any difficulties, surprises etc that you encounter

    That way you will:

    * Eventually learn how to do it for yourself - its how most of us learned
    * Get faster results
    * And, probably, get more support - it is so much more satisfying to help someone who is trying for themselves.

    A quick search of the Internet will also turn up quite a few good books on Excel and VBA.

    Written in a constructive spirit

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Martin

    I have tried to write code to do the job, but cannot get it to work properlp-see my code below

    It would be appreciated if you would amend the code to select the month number from the headings Col C to Z and then move the cols containing the month selected to Col C & D and then clear the balance of the cols from E onwards

    Code:
     Sub Move_Cols()
    Dim Lr As Long
    Dim Cnt As Long
    Dim i As Long
    
    Dim currMonth As Long
    On Error Resume Next
    Cnt = Sheets.Count
    Application.DisplayAlerts = False
        For i = Cnt - 1 To 11 Step -1
    
    
    currMonth = InputBox("Input Current Month", "Current Month", 1)
     With ActiveSheets
         Lr = .Cells(Rows.Count, "C").End(xlUp).Row
        .Range("E1:Z1") = "<>" & currMonth
        .Range("E1:z" & Lr).Copy
        .Range("C1:D1").Paste
        
        End With
       Next i
    End Sub

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Martin

    Thanks for getting me to write my own code. I spent a few hours today developing some code for another workbook and I learnt a lot when debugging the code

    Howard

  5. #5
    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
    Are you looking for something like this?
    [There is no need to move the any columns to Cols C&D if you are going to delete the other columns, you only need to delete the columns and the ones remaining will be in C&D]

    Code:
    Option Explicit
    Sub DeleteCols()
      Dim iMonth As Integer
      Dim iWks As Integer
      Dim iCol As Integer
      Dim sMonth As String
      Dim iLen As String
      
      iMonth = InputBox("Input Current Month", "Current Month", 1)
      sMonth = iMonth & "/"
      iLen = Len(sMonth)
      Application.ScreenUpdating = False
      For iWks = 2 To Worksheets.Count - 1
        For iCol = 26 To 3 Step -1
          With Worksheets(iWks).Cells(1, iCol)
            If Left(.Value, iLen) <> sMonth Then
              .EntireColumn.Delete
            End If
          End With
        Next
      Next
      Application.ScreenUpdating = True
    End Sub
    Test it on a copy to ensure it does what you want.

    Steve

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2013-11-30)

  7. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help, much appreciated

    Howard

Posting Permissions

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