copying a column to another worksheet but each row should contain only 10 columns

[Davidex]

Hi,
I recently posted a query but it was more like a project. I am solving one by one. I have already sorted column A to C based on column A values. Now I need to copy and paste column A in Sheet2. But in Sheet2 I want each row should contain only 10 values, the remaining values should go into the first 10 columns of Row2 and so on. Any Ideas? Thanks!

 

[Davidex]

How many headers are there? Can you show me a sample of the structure? Are you trying to copy each row to a new sheet?

Hi sheetspread,

I have four columns: My Items, Locations, Comments and Master Locations(Unique)
In my Items I type all my personal stuff
Locations column is for where my stuff is placed
Comments: if any
Master Locations: Unique values of all my locations like cupboard 1st shelf, Locker etc

When I open the workbook, I am sorting everything in My Items column (A) and place them ten in a row in Sheet2. This is done basically so that I can see all my stuff in one screen. I have worked out this so far.

Now I need to draw an autoshape in Sheet 3 (one shape for every Master Location (column D) so that when I click on any column in Sheet 2 (my Stuff displayed in one screen), I am taken to Sheet 3 where only the autoshape of where my stuff is in, is highlighted. This is basically the program I am working on.

How do I draw an autoshape and place them in sheet 3 in precise locations?

Thanks for your interest and contribution.

 

[sheetspread]

Something like:

Sub drawshap()
Sheets("Sheet3").Select
ActiveSheet.Shapes.AddShape(msoShapeOval, 161.25, 128.25, 183, 99.75).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Ellipse"
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 4
Selection.ShapeRange.IncrementLeft 200
Selection.ShapeRange.IncrementTop 30
End Sub

 

[Davidex]

Something like:

Sub drawshap()
Sheets("Sheet3").Select
ActiveSheet.Shapes.AddShape(msoShapeOval, 161.25, 128.25, 183, 99.75).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Ellipse"
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 4
Selection.ShapeRange.IncrementLeft 200
Selection.ShapeRange.IncrementTop 30
End Sub

Hi sheetspread,

Thanks for the reply. I learnt something new from your reply i.e.

Selection.ShapeRange.IncrementLeft 200 Selection.ShapeRange.IncrementTop 30

But as the Locations are fixed and are very few, I decided to draw them manually and highlight the same. Now whenever I open the Workbook, the sheet1.Column A is sorted automatically and the values are pasted 10 in a Row in Sheet 2 (Thanks for your code). Then when I click on any cell that contains a value, it takes me to Sheet3 where the corresponding Autoshape is highlighted. My project is now complete. Thanks again sheetspread.