+ Reply to Thread
Results 1 to 8 of 8

Find a column by title even if its been moved and show contents on another sheet

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-14-2012
    Location
    England
    MS-Off Ver
    Excel 2010/2013
    Posts
    100

    Find a column by title even if its been moved and show contents on another sheet

    Hi, I'm trying to make sure I can copy a column to another worksheet even if the column has been moved in original sheet.
    In other words copy column 5 from main sheet to second sheet even if column 5 is called the same but has moved to column 9

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Find a column by title even if its been moved and show contents on another sheet

    do you mean actually copy, or just reference the values from it?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find a column by title even if its been moved and show contents on another sheet

    If you create a Dynamic Named Range for the column, you can always reference it by name...even if it moves.

    Example:
    • Select Col_E on a Sheet1
    • Insert.Name.Define
    ...Names in Workbook: SourceColumn
    ...Refers to: =Sheet1!$E:$E
    ...Click: OK

    Now, from anyplace in the workbook you can press F5 and choose SourceColumn...and it will select the SourceColumn range.
    If you insert columns before Sheet1!$E:$E so that data now exists in Col_M, SourceColumn will refer to Sheet1!$M:$M.
    You can also use the SourceColumn as a range reference in VBA.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Contributor
    Join Date
    05-14-2012
    Location
    England
    MS-Off Ver
    Excel 2010/2013
    Posts
    100

    Re: Find a column by title even if its been moved and show contents on another sheet

    Thanks! I wouldn't have thought of that but I've already defines names so of course! Could you please give an example of how I can use it in VBA?

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find a column by title even if its been moved and show contents on another sheet

    Quote Originally Posted by KAPearson View Post
    Thanks! I wouldn't have thought of that but I've already defines names so of course! Could you please give an example of how I can use it in VBA?
    I believe something like this would work...
    [SourceColumn].Copy
    ...or...this:
    Range("SourceColumn").Copy
    Does that help?

  6. #6
    Forum Contributor
    Join Date
    05-14-2012
    Location
    England
    MS-Off Ver
    Excel 2010/2013
    Posts
    100

    Re: Find a column by title even if its been moved and show contents on another sheet

    Ill give the code so you might be able to see what Im doing.
    I filter a column in Main then copy it to main2 but if the column moves...

    Dim Dept As String
    Dim wsM As Worksheet, wsM2 As Worksheet
    Dim LRow2 As String
    
    Private Sub BakeryData()
    Set wsM = Sheets("Main")
    Set wsM2 = Sheets("Main2")
        With wsM2
            'Dept Specific
            wsM.Columns("bm:bm").Copy .Range("j1")
            wsM.Columns("bn:bn").Copy .Range("k1")
            wsM.Columns("bo:bo").Copy .Range("l1")
            wsM.Columns("bp:bp").Copy .Range("m1")
            'auto fit titles
            .Rows(1).AutoFit
        End With
    End Sub
    
    
    Private Sub CB_Bakery_Click()
    Application.ScreenUpdating = False    ' turns off screen refreshing.
    Dept = "Bakery"
    'sets the variables wsM equal to Main worksheet and wsM2 equal to Main2
    Set wsM = Sheets("Main")
    Set wsM2 = Sheets("Main2")
    LRow2 = 0
    'reset main
    Sheets("main").Select
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
        'select the Main2 worksheet
        With wsM2
            wsM2.UsedRange.Clear
            'filter Dept
            wsM.UsedRange.AutoFilter Field:=3, Criteria1:=Dept
            wsM.Columns("b:b").Copy .Range("a1")
            wsM.Columns("d:d").Copy .Range("b1")
            wsM.Columns("f:f").Copy .Range("c1")
            wsM.Columns("q:q").Copy .Range("e1")
            wsM.Columns("r:r").Copy .Range("g1")
            wsM.Columns("g:g").Copy .Range("h1")
                'Dim DeptData As String
                'DeptData = Dept & "Data"
                Call BakeryData
                Call Think25
                Call Fire
                Call CPRs
            If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
            Sheets("main2").Select
            Application.ScreenUpdating = True    ' turns back on screen refreshing.
        End With
    End Sub
    
    
    Private Sub Think25()
    'sets the variables wsM equal to Main worksheet and wsM2 equal to Main2
    Set wsM = Sheets("Main")
    Set wsM2 = Sheets("Main2")
    
    'sets variable LRow2 equal to the last row in column A of the
    'activesheet that contains data
    LRow2 = wsM2.Cells(wsM2.Rows.Count, "A").End(xlUp).Row
            
    ' Set D column Due for Think 25
    'put a value in column D cell D1
    wsM2.Range("D1").Value = "Due"
    'value in D2 equal to the formula
    'copy the formula
    wsM2.Range("D2:D" & LRow2).Formula = "=c2-(TODAY()-182)"
    'set conditional formating
        With wsM2.Range("D2:D" & LRow2)
            .FormatConditions.Delete
            '.Range("d2,d" & LRow2).NumberFormat = "0"
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                Formula1:="30"
            .FormatConditions(1).Font.ColorIndex = 15
            .FormatConditions(1).Interior.ColorIndex = 2
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
                Formula1:="1", Formula2:="30"
            .FormatConditions(2).Font.ColorIndex = 2
            .FormatConditions(2).Interior.ColorIndex = 37
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
                Formula1:="=TODAY()-182"
            .FormatConditions(3).Font.ColorIndex = 2
            .FormatConditions(3).Interior.ColorIndex = 9
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    End Sub
    
    Private Sub Fire()
    'sets the variables wsM equal to Main worksheet and wsM2 equal to Main2
    Set wsM = Sheets("Main")
    Set wsM2 = Sheets("Main2")
    'sets variable LRow2 equal to the last row in column A of the
    'activesheet that contains data
    LRow2 = wsM2.Cells(wsM2.Rows.Count, "A").End(xlUp).Row
    ' Set F Column Due for Fire
    'put a value in column D cell D1
    wsM2.Range("f1").Value = "Due"
    'value in f2 equal to the formula
    'copy the formula
    wsM2.Range("f2:f" & LRow2).Formula = "=e2-(TODAY()-365)"
    'set conditional formating
        With wsM2.Range("f2:f" & LRow2)
            .FormatConditions.Delete
            .Range("f2,f" & LRow2).NumberFormat = "0"
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                Formula1:="30"
            .FormatConditions(1).Font.ColorIndex = 15
            .FormatConditions(1).Interior.ColorIndex = 2
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
                Formula1:="1", Formula2:="30"
            .FormatConditions(2).Font.ColorIndex = 2
            .FormatConditions(2).Interior.ColorIndex = 37
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
                Formula1:="=TODAY()-365"
            .FormatConditions(3).Font.ColorIndex = 2
            .FormatConditions(3).Interior.ColorIndex = 9
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    End Sub
    
    Private Sub CPRs()
    'sets the variables wsM equal to Main worksheet and wsM2 equal to Main2
    Set wsM = Sheets("Main")
    Set wsM2 = Sheets("Main2")
    'sets variable LRow2 equal to the last row in column A of the
    'activesheet that contains data
    LRow2 = wsM2.Cells(wsM2.Rows.Count, "A").End(xlUp).Row
    ' Set I Column Due for CPrs
    'put a value in column D cell D1
    wsM2.Range("i1").Value = "Due"
    'value in i2 equal to the formula
    'copy the formula
    wsM2.Range("i2:i" & LRow2).Formula = "=h2-(TODAY()-182)"
    'set conditional formating
        With wsM2.Range("i2:i" & LRow2)
            .FormatConditions.Delete
            .Range("i2,i" & LRow2).NumberFormat = "0"
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                Formula1:="30"
            .FormatConditions(1).Font.ColorIndex = 15
            .FormatConditions(1).Interior.ColorIndex = 2
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
                Formula1:="1", Formula2:="30"
            .FormatConditions(2).Font.ColorIndex = 2
            .FormatConditions(2).Interior.ColorIndex = 37
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
                Formula1:="=TODAY()-182"
            .FormatConditions(3).Font.ColorIndex = 2
            .FormatConditions(3).Interior.ColorIndex = 9
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
            'set widths
            wsM2.Columns("A:A").ColumnWidth = 15
            wsM2.Columns("B:B").ColumnWidth = 12
            wsM2.Range("C:C,E:E").ColumnWidth = 7.5
            wsM2.Columns("D:D").ColumnWidth = 4
            wsM2.Columns("F:F").ColumnWidth = 4
            wsM2.Range("G:G,H:H").ColumnWidth = 7.5
            wsM2.Columns("I:I").ColumnWidth = 4
            wsM2.Range("J:J,K:K,L:L,M:M").ColumnWidth = 7.5
            '.Columns("F:F").EntireColumn.AutoFit
    End Sub
    
    Private Sub CB_ChooseTraining_Click()
    ChooseT.Show
    End Sub
    
    Private Sub CB_DevPlans_Click()
    'sets the variables wsM equal to Main worksheet and wsM2 equal to Main2
    Set wsM = Sheets("Main")
    Set wsM2 = Sheets("Main2")
    LRow2 = 0
    'reset main
    Application.ScreenUpdating = False    ' Prevents screen refreshing.
    Sheets("main").Select
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    'select the Main2 worksheet
        With wsM2
            wsM2.UsedRange.Clear
            'select all the cells
            wsM.UsedRange.AutoFilter Field:=4, Criteria1:="=Manager", Operator:=xlOr, _
            Criteria2:="=Team Leader"
            
            wsM.Columns("b:b").Copy .Range("a1")
            wsM.Columns("d:d").Copy .Range("b1")
            wsM.Columns("fb:fb").Copy .Range("c1")
            wsM.Columns("et:et").Copy .Range("d1")
            wsM.Columns("er:er").Copy .Range("e1")
            wsM.Columns("es:es").Copy .Range("f1")
            wsM.Columns("eu:eu").Copy .Range("g1")
            wsM.Columns("ev:ev").Copy .Range("h1")
            wsM.Columns("ew:ew").Copy .Range("i1")
            wsM.Columns("ex:ex").Copy .Range("j1")
            wsM.Columns("ey:ey").Copy .Range("k1")
            wsM.Columns("ez:ez").Copy .Range("l1")
    'sets variable LRow2 equal to the last row in column A of the
    'activesheet that contains data
    LRow2 = wsM2.Cells(wsM2.Rows.Count, "A").End(xlUp).Row
            'set widths
            .Columns("A:A").ColumnWidth = 15
            .Columns("B:B").ColumnWidth = 12
            .Range("C:M").ColumnWidth = 9.2
            .Rows(1).AutoFit
            '.Columns("F:F").EntireColumn.AutoFit
        End With
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Sheets("main2").Select
    End Sub
    
    Private Sub CB_Produce_click()
     
    End Sub

  7. #7
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find a column by title even if its been moved and show contents on another sheet

    Assuming you want to autofilter on the "Dept" column, you need to determine which ordinal position in the filter data headings contains "Dept".
    Try something like this...
    With the headings in Row_1
    Dim RowPos as Integer
    RowPos = WorksheetFunction.Match(Dept,[1:1],0)
    Then use RowPos in your AutoFilter code.

  8. #8
    Forum Contributor
    Join Date
    05-14-2012
    Location
    England
    MS-Off Ver
    Excel 2010/2013
    Posts
    100

    Re: Find a column by title even if its been moved and show contents on another sheet

    Thank you Ron, I'll give that a go tomorrow now as its latee over here in the UK.
    I'll get back to you. Thanks and goodnight.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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