PDA

View Full Version : Copy Transpose and Paste Macro



MJKeenan
04-25-2017, 09:19 PM
Hello, I'm working on a new tab in a workbook I created and I need to automate populating the data from rows in one tab to columns with a fill down of the ID and prices in another tab.

I attached the workbook. The data from the MP Tab is in rows starting at P3 and I need it transposed to the OF Template MP tab for the 1st ID 500805299 in the new tab in column E for all 8 rows and then put the prices in the corresponding rows 1-8 in Column I. Then repeat for the next ID in the MP Tab under the previous ID completed. I hope this makes sense as I have tried to do this with an offset but failed to make it work. Any help is greatly appreciated

Thanks!

p45cal
04-26-2017, 02:52 AM
Select the databody of your sourcetable (excluding row and column headers (highlighted in green in the attached)) and run the blah macro (or click the button). It will add a new sheet with data on.
The code in the attached is:
Sub blah()
Set DataRange = Selection
HeadersRow = DataRange.Row - 1
HeadersColumn = DataRange.Column - 1
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
Set Destn = NewSht.Cells(1)
With DataRange.Parent
For Each cll In DataRange.Cells
Set Destn = Destn.Offset(1)
Destn.Value = .Cells(cll.Row, HeadersColumn).Value
Destn.Offset(, 1).Value = .Cells(HeadersRow, cll.Column).Value
Destn.Offset(, 2).Value = cll.Value
Next cll
End With
End Sub

MJKeenan
04-26-2017, 06:04 AM
Wow, thank you so much!!! I really appreciate this a lot! I'll try it this morning thanks!

MJKeenan
04-26-2017, 06:17 AM
Sorry, this didn't work. I need the data to transpose over to the MP tab from the OF Template MP tab not a new tab. Then I need the ID to autofill down for every size and fill in the prices associated with the sizes. Is this possible?

Thanks

p45cal
04-26-2017, 07:58 AM
Sub blah()
Set DataRange = Selection
HeadersRow = DataRange.Row - 1
HeadersColumn = DataRange.Column - 1
Set destn = Sheets("MP").Range("B5")
With DataRange.Parent
For Each cll In DataRange.Cells
Set destn = destn.Offset(1)
destn.Value = .Cells(HeadersRow, cll.Column).Value
destn.Offset(, 3).Value = .Cells(cll.Row, HeadersColumn).Value
destn.Offset(, 7).Value = cll.Value
Next cll
End With
End Sub
It overwrites whatever's in column B of the MP sheet.

MJKeenan
04-26-2017, 09:15 AM
Sorry that didn't work for some reason it is not pasting the ID 500805299 under the ID header (Column E) and filling down. It is not putting the prices in the Wholesale (Column I).

p45cal
04-26-2017, 09:56 AM
You are selecting the range (highlighted in green) first, before runnng the macro, aren't you?

MJKeenan
04-26-2017, 10:37 AM
Yes but the price is populating in Column H and it should populate in Column I. The ID should fill down too. It is really close. I tried to put a line of code in it for the macro to select the highlighted green area but it didn't work so I commented it out. Is there a way for the macro to select that highlighted in green area first? Thanks

MJKeenan
04-26-2017, 10:58 AM
19031I need the MP tab to look like this at the end of the macro.

p45cal
04-26-2017, 11:54 AM
Before:
19036
After:
19037


Is there a way for the macro to select that highlighted in green area first?

Sub blah()
Application.Goto Sheets("OF Template MP").Range("Q3:X4")
Set DataRange = Selection
....

MJKeenan
04-26-2017, 12:40 PM
Thank you so much!!!!! OMG you saved my day!!!!! I program in sql so vba is kinda new to me and I really appreciate your patience with me and my vba question. Wow, this is awesome!!!!!

p45cal
04-26-2017, 11:57 PM
You've got to read up rules on forum posting; you've cross posted this question here without providing links to all your cross posts wherever they may be:
https://www.mrexcel.com/forum/excel-questions/1002416-transpose-data-rows-column-one-tab-another.html


have a read of http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

It's important for you. I, for one, once I've discovered a cross-poster who insists on not providing links, never help that person again. And I do check. It's so miserableto spend significant time for someone to find they already had the anser elsewhere.