Forum Discussion

denisetiburzivc's avatar
denisetiburzivc
New Member
1 year ago

Some help with transferring columns between sheets

Hi!

So I have an access to a excel sheet from my company. But in this file has only the last five months and currently month, each one is a column.

But I need analysis the currently year, so I want some way to, automatically transfer de oldest month column to another sheet. 

Need Help
Tutorial Requests

2 Replies

  • elitesmitpatel's avatar
    elitesmitpatel
    Solution Supplier
    1 year ago

    1. Press Alt + F11 to open the Visual Basic for Applications editor.

    2.In the editor, go to Insert > Module and paste the following code:

    Sub TransferOldestMonthColumn()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastColumn As Integer
    Dim oldestMonthColumn As Integer

     

    ' Replace "Sheet1" with the actual name of your source sheet
    Set wsSource = ThisWorkbook.Sheets("Sheet1")

    ' Find the last column with data
    lastColumn = wsSource.Cells(1, Columns.Count).End(xlToLeft).Column

    ' Determine the oldest month column based on column headers
    oldestMonthColumn = Application.Match(WorksheetFunction.Min(wsSource.Range(Cells(1, 1), Cells(1, lastColumn))), wsSource.Range(Cells(1, 1), Cells(1, lastColumn)), 0)

    ' Create a new sheet for the transferred data
    Set wsTarget = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsTarget.Name = "Oldest Month Data"

    ' Transfer the oldest month column to the new sheet
    wsSource.Columns(oldestMonthColumn).Copy wsTarget.Columns(1)

    ' Delete the oldest month column from the source sheet
    wsSource.Columns(oldestMonthColumn).Delete
    End Sub

    3.Close the VBA editor and press Alt + F8 to run the macro.

    4.This macro will:

    • Identify the oldest month (leftmost column).
    • Move it to another sheet (e.g., Sheet2).
    • Delete the oldest month column from the current sheet.

    You can adjust the sheet names ("Sheet1" and "Sheet2") as needed.

     

    2. Using Power Query (Manual Process)

    Alternatively, you can use Power Query to transfer the oldest month column manually:

    1. Load Data into Power Query:
      • Select your data and go to Data > Get & Transform Data > From Table/Range.
    2. Remove the Oldest Month:
      • In Power Query, find the column that represents the oldest month, right-click, and select Remove.
    3. Load to Another Sheet:
      • After removing the column, load the updated table back to your Excel sheet.
      • Then, save the removed column by loading it to a new sheet in Excel.
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi denisetiburzivc ,

    I haven't heard back from you in a while and wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
    Looking forward to your reply!