1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Macro to convert Rows from one sheet to columns in another sheet

Discussion in 'Business Applications' started by mihaufo, Mar 15, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. mihaufo

    mihaufo Thread Starter

    Joined:
    Mar 15, 2012
    Messages:
    41
    Hi,

    I am trying to classify and code my inventory items in excel.
    My classification sheet contains data in columns which I would like to copy to rows in another excel sheet.
    Here is the sample data in ACMOTORS-ATTRIBUTES sheet:

    The above sheet has to be converted to columns as in Item_Classificatios_Form sheet :

    Can anybody assist me in writing a macro for the same.

    Thanks,
    mihaufo
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi, welcome to the forum,
    Have you tried recording a macro?
    It would me two macros really:
    1. Record a new macro, name the first one Step1
    What you do is then select the header row A1:M1 go to the destination sheet and place the cursor in the empty cell you need and select paste special and click Values and Transpose
    then stop recording.
    2. record a new macro name it Step2 and the select the row you need Ax:Mx go to the destination sheet and selct the empty cell next to "Motor Rating (KW) and do the same, Paste Special, Values and Transpose.
    Stop the recording.

    Open the VBA editor and check (probably it's named Module1) the macro code that has been created, with a lilltle bit of VBA you can change the code to make it useable for other rows especially Step2
    Try it keep us informed, if you do it yourself first it's easier to understand :)
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You can do Item_Code with a single formula -- see the yellow cells.

    Classification_Code is nothing.

    You can do Attribute_Code with a single (array) formula -- see the green cells.

    Attribute_Value is the fun one. :D A lot of the work is already done in Auto Generated Description.

    Try the test macro in the attached, is the result OK for your purposes?

    Sub test()
    For Each Cell In Sheets("Sheet1").Range("N2:N" & WorksheetFunction.CountA(Sheets("Sheet1").Columns(14)))
    x = Replace(Cell, "MOTOR AC, ", "") & ", " & Cell.Offset(, -2) & ", " & Cell.Offset(, -1)
    Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Resize(13) = _
    Application.Transpose(Split(x, ","))
    Next Cell
    End Sub
     

    Attached Files:

  4. mihaufo

    mihaufo Thread Starter

    Joined:
    Mar 15, 2012
    Messages:
    41
    Thanks for the help!
    I tried and all the functions and macro work except Transpose. Attribute_Code is not working, it is not running the function. Kindly help.
     
  5. mihaufo

    mihaufo Thread Starter

    Joined:
    Mar 15, 2012
    Messages:
    41
    Thanks Keebellah,
    I will try it later because its urgent for me to submit the sheet
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I don't understand "not running the function". Do you mean it's returning an error and stopping, or just not doing anything?

    Edit: the code was just for building column E on Sheet 2, as an example. Did you realise that?
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Here's the whole thing.

    Sub test()
    Sheets("Sheet2").Select
    For Each Cell In Sheets("Sheet1").Range("N2:N" & WorksheetFunction.CountA(Sheets("Sheet1").Columns(14)))
    x = Replace(Cell, "MOTOR AC, ", "") & ", " & Cell.Offset(, -2) & ", " & Cell.Offset(, -1)
    Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(13) = _
    Application.Transpose(Split(x, ","))
    Next Cell
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
    Range("C3:C15") = Application.Transpose(Sheets("Sheet1").Range("A1:M1"))
    Range("C3:C15").AutoFill Destination:=Range("C3:C" & LastRow), Type:=xlFillDefault
    Range("B3:B" & LastRow) = "MOTOR AC"
    Range("A3:A" & LastRow).FormulaR1C1 = "=1000000+ROUNDUP((ROW()-2)/13,0)-1"
    Range("A3:A" & LastRow).Value = Range("A3:A" & LastRow).Value
    End Sub
     

    Attached Files:

  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1045277

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice