Results 1 to 2 of 2

Thread: Excel: Macro: convert text to text

  1. #1

    Excel: Macro: convert text to text

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,
    I am very new to marco.
    I have XL document with 50 tabs. Each tab contains one column, and n rows. I need to change those rows (not all rows) and copy into different column. Please see example in attached xl.
    The column A is the input, and column B is the desired output. Thanks for your help.


    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    I think you attached the wrong sheet?

    I think this is what you are after. Although not certain about your "not all rows" comment

    Input sheet looks like this
    sheet Header Column
    Sheet2 abc =MATCH(B2,ColHeader)
    Sheet3 def =MATCH(B3,ColHeader)

    and colHeader is a named range in output A1..AX1
    and calling the input and Output sheets respectively
    Assuming first row is a header and is not copied

    Sub CopyTo()
    Dim i As Long, nRows As Long
    Dim iData
        nRows = Sheets("input").Range("a2").End(xlDown).Row - 1
        iData = Sheets("input").Range("a2").Resize(nRows, 3).Value2
        For i = 1 To UBound(iData, 1)
            nRows = Sheets(iData(i, 1)).Cells(Rows.Count, 1).End(xlUp).Row - 1
            Sheets("Output").Cells(2, iData(i, 3)).Resize(nRows, 1).Value2 = _
                Sheets(iData(i, 1)).Cells(2, 1).Resize(nRows, 1).Value2
    End Sub
    Last edited by WizzardOfOz; 2014-11-11 at 01:06 AM.

Posting Permissions

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