Macro for Copying group of cells and paste as row
Closed
vaimrankhan
Posts
7
Registration date
Wednesday November 8, 2017
Status
Member
Last seen
December 13, 2017
-
Nov 8, 2017 at 02:00 AM
Blocked Profile - Dec 13, 2017 at 04:12 PM
Blocked Profile - Dec 13, 2017 at 04:12 PM
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.
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.
Related:
- Macro for Copying group of cells and paste as row
- Saints row 2 cheats - Guide
- Protect pdf from copying - Guide
- Excel macro to create new sheet based on value in cells - Guide
- How to delete a row in a table in word - Guide
- Spell number in excel without macro - Guide
2 responses
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
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
So did you try the import?
vaimrankhan
Posts
7
Registration date
Wednesday November 8, 2017
Status
Member
Last seen
December 13, 2017
Nov 11, 2017 at 11:34 PM
Nov 11, 2017 at 11:34 PM
I did not understand.
vaimrankhan
Posts
7
Registration date
Wednesday November 8, 2017
Status
Member
Last seen
December 13, 2017
>
Blocked Profile
Nov 15, 2017 at 12:38 AM
Nov 15, 2017 at 12:38 AM
Sorry, I tried doing as you had instructed. However, i would like to repeat myself.
InfoPath form is saved in Share Point > I Export the data from SharePoint > Then My The Extracted data is as below.
Staff Name|Company|Designation|In Date|In Time|Out Date|Out Time|Staff Name|Company|Designation|In Date|In Time|Out Date|Out Time
Imran Khan|Company|Designation|Date |07:00 |Date |15:30pmImran Khan|Company|Designation|Date |07:00 |Date |15:30pm
Output i want to use Macro and get is as below.
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
Note: I do not have delimiters in the exported data, all the data is in linear format.
InfoPath form is saved in Share Point > I Export the data from SharePoint > Then My The Extracted data is as below.
Staff Name|Company|Designation|In Date|In Time|Out Date|Out Time|Staff Name|Company|Designation|In Date|In Time|Out Date|Out Time
Imran Khan|Company|Designation|Date |07:00 |Date |15:30pmImran Khan|Company|Designation|Date |07:00 |Date |15:30pm
Output i want to use Macro and get is as below.
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
Note: I do not have delimiters in the exported data, all the data is in linear format.
vaimrankhan
Posts
7
Registration date
Wednesday November 8, 2017
Status
Member
Last seen
December 13, 2017
Dec 11, 2017 at 02:48 AM
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
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
Nov 8, 2017 at 11:59 PM
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