Menu
Ask a question Report Reply to this topic

Macro for Copying group of cells and paste as row

vaimrankhan 7 Posts Wednesday November 8, 2017Registration date December 13, 2017 Last seen - Nov 8, 2017 at 02:00 AM - Latest reply: ac3mark 9199 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen
- Dec 13, 2017 at 04:12 PM
Excel Ask a question Report Reply to this topic
Hi,

I need some help i need a Macro Code to arrange certain cells and prepare it as a row.

I export the file from SharePoint in Excel Format. The data of the form is in Linear.
The data comes in one row. However I need to transpose and get the Set of Cells in Row By Row.

For Example: J1:R9 is one set then S1:AA1 must come below. Its a pattern of 9 Fields.

Please help.
See more 

11 replies

Reply to this topic
Report Respond to ac3mark
ac3mark 9199 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen - Updated by ac3mark on 8/11/17 at 04:33 PM
0
Helpful
1
OK, Is the file always named the same? If not, then copy it locally, and name it SHAREPOINTINPUT.

Now, open up a new workbook, and click on Data>import from text.

When it asks how is the file formatted, select "SET FIELD LENGTHS". In th enext screen set up the string with 9 character delimiters. Save it.

Now, work the data. Save it as something else.

Now, when you get a new file, name it the same, place it in th esame directory, open up the workbook, and hit F9 (refresh data).

Post back if you have different specifications (I expect the scope will expand!)

Have fun!

It's kind of fun to do the impossible! -Walter Elias Disney
Report Respond to vaimrankhan
vaimrankhan 7 Posts Wednesday November 8, 2017Registration date December 13, 2017 Last seen - Nov 8, 2017 at 11:59 PM
Hi Mark,

Thanks for replying. I am new to Macros. However let me share how exactly my data is.

Staff Name|Company|Designation|In Date|In Time|Out Date|Out Time|Staff Name|Company|Designation|In Date|In Time|Out Date|Out Time and so on.

Output i want is in another sheet.
Staff Name|Company|Designation|In Date|In Time|Out Date|Out Time
Imran Khan|Company|Designation|Date |07:00 |Date |15:30pm
Imran Khan|Company|Designation|Date |07:00 |Date |15:30pm
Imran Khan|Company|Designation|Date |07:00 |Date |15:30pm
Respond to ac3mark
Report Respond to ac3mark
ac3mark 9199 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen - Nov 9, 2017 at 04:46 PM
0
Helpful
8
So did you try the import?
View all 8 comments
Report Respond to ac3mark
ac3mark 9199 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen - Nov 22, 2017 at 04:20 PM
Linear format means set delimiters. Setup the delimiters manually on the import!
Report Respond to vaimrankhan
vaimrankhan 7 Posts Wednesday November 8, 2017Registration date December 13, 2017 Last seen - Dec 11, 2017 at 02:48 AM
Hi Mark, I have recorded the below Macro. All i want is to Loop it till the last.

Sub MainTranspose()
'
' MainTranspose Macro
'
' Keyboard Shortcut: Ctrl+t
'
Sheets("Raw").Select
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(1, 0).Range("Table_owssvr3[#Headers,[Name]:[Form Name]]"). _
Select
Selection.Copy
Sheets("Macro").Select
ActiveSheet.Paste
Sheets("Raw").Select
ActiveCell.Offset(0, 5).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Macro").Select
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveCell.Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveCell.Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveCell.Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]:[CC 1]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveCell.Offset(0, 8).Range("A1").Select
Selection.End(xlUp).Select
ActiveSheet.Previous.Select
ActiveCell.Offset(0, 8).Range("Table_owssvr3[#Headers,[Name]]").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveCell.Range("A1:A10").Select
Application.CutCopyMode = False
Selection.FillDown
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlToLeft).Select
ActiveCell.Range("A1:E10").Select
Selection.FillDown
End Sub
Report Respond to ac3mark
ac3mark 9199 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen - Dec 11, 2017 at 04:30 PM
Well initialize a variable, lets call it "rowscount"

Now, set the variable with a value, in this case, it will be the number of empty cells in column "A". We perform that task with the following command:
rowscount = Cells(ThisWorkbook.Worksheets("YOUR_WORKSHEET_NAME").Rows.Count, 1).End(xlUp).Row

Now, loop using the value of rowscount. You do know how to construct a loop, don't you?
Report Respond to vaimrankhan
vaimrankhan 7 Posts Wednesday November 8, 2017Registration date December 13, 2017 Last seen - Dec 13, 2017 at 07:51 AM
Dear Mark,

Thank you for replying. I am actually new to this Excel and Macro.
It's very challenging for me to do this. I got no friends or anyone in my work place to help me with this macro.
It shall be helpful, if you help me code this with the loop code.
I desperately need this as I got very less time to show case this.
Thank you in advance.
Report Respond to ac3mark
ac3mark 9199 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen - Dec 13, 2017 at 04:12 PM
Well a standard loop based on the above example could be:

Dim rowCNT
for rowCNT = 0 to rowscount
'do your code here using the variable of rowCNT as in
msgbox(rowCNT & " of " & rowscount & " rows.")
next
Respond to ac3mark
Ask a question