Solved: To split the one table into multiple table

[Rakesh]

Hi Rocks,

I had a spread sheet which contains multiple columns. But I have to split it into three five column as one table and second three column into another table likewise etc.,. The first column (i.e, Stub) should be same for all table.

I am unable to attach the sample file. So I explain as below.

SAMPLE TEXT

Column A Column B Column C Column D Column E Column F Column G
Text Values Values Values Values Values Values

OUTPUT TEXT

Column A Column B Column C Column D
Text Values Values Values

Column A Column E Column F Column G
Text Values Values Values

Can it possible to create a coding to do this?



Thanks in advance
Rakesh

[Bob Phillips]

All on the same sheet or separate sheets?

[Rakesh]

Separate Sheets

[Bob Phillips]

Public Sub ProcessData()
Dim sh As Worksheet
Dim Lastrow As Long
Dim i As Long
Dim cell As Range

Application.ScreenUpdating = False

Set sh = Worksheets("Sheet1")
With Worksheets("Sheet2")

Lastrow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
sh.Range("A1").Resize(Lastrow, 7).Copy .Range("A1")
For i = Lastrow To 2 Step -1

.Rows(i + 1).Insert
.Cells(i, "A").Copy .Cells(i + 1, "A")
.Cells(i, "E").Resize(, 3).Copy .Cells(i + 1, "B")
.Cells(i, "E").Resize(, 3).ClearContents
Next i
End With

Application.ScreenUpdating = True
End Sub

[Rakesh]

Hi,

Thanks for your kind response.

I have tested the macro. Its not working. Its paste the data into a single sheet.

The Attached file shows you the sample and Macro’s Output for your reference


Thank You,
Rakesh

[Bob Phillips]

That output looks like a single sheet to me.

[Aussiebear]

I think the OP wants it on separate sheets if possible

[Bob Phillips]

That is what he said, but then he showed an example on one sheet, just like his original example. I have stopped second-guessing and am waiting for clear definition.

[Rakesh]

Hi,

As per Aussiebear guessing it should be in Separate Sheets.

Thanks,
Rakesh

[Aussiebear]

Hi Rakesh, XLD's comment was not primarily aimed at whether I was "guessing" but referred to the example you provided as all being on the single sheet. Please repost your sample showing the resulting outlay you require. ( including samples on more than one sheet)

[Rakesh]

Hi Aussiebear,

I Feel So far I am in the wrong way. I extremely sorry for that. I better explain you my requirement.

1. Assume spreadsheet Values contains more than 30 column.
2. Stub remains same for all tables.
3. Each table should contain Stub and 5 value columns.
4. Each table should be in seperate sheets.

Attached file is a Output Sample for your reference.

If you have any confusion in this thread, Please let me know.

Thanks,
Rakesh

[Bob Phillips]

I am sorry Rakesh, I am trying to help, but you are just confusing me. I cannot see any correlation between that spreadsheet and your original one or any of your layouts. And I cannot work out what is the input and what is the output in that.

[Aussiebear]

Well I thought I knew what you were asking for, until I read your last post. I have no idea what you mean by "Stub".

1.Assume spreadsheet Values contains more than 30 column.
By this I assume you mean data will fill columns A to AD.

2. Stub remains same for all tables.
I'm guessing here that you are referring to the row headers in A4 to A8

3. Each table should contain Stub and 5 value columns.
Did you mean 5 rows of values?

4. Each table should be in seperate sheets.
Got that bit from the start.

Have a look at the attached worksheet and confirm if the sheets "Output 1 to Output 6" are the end result you are chasing? if so then I'm sure we can come to a solution.

[Rakesh]

Hi,

Thanks for spending your valuable time for me.

Sorry to so for confusing you.

Moreover you got it, what I trying to explain.

Data will fill columns A to AE
Stub is nothing data of Column A

Output should be in new worksheets and each table should be in separate sheets

Output 1 – Column A and Column B to Column F
Output 2 – Column A and Column G to Column K
Output 3 – Column A and Column L to Column P
Output 4 – Column A and Column Q to Column U
Output 5 – Column A and Column V to Column Z
Output 6 – Column A and Column AA to Column AE

More details go through the attachments


Thanks,
Rakesh

[Aussiebear]

I recognise that there will often be a communication breakdown between users from different nations, but can I ask that you consider stop using the word "stub"? A spreadsheet has both column and row identifiers and Excel enables the Users to define Column & Row headings. What you are referring to as "stub" is a group of Row headings. These row headings clarify the data contained within their individual rows, and as a result are not "Stub is nothing data of Column A". When everyone tries to use the same terminology, most of the communication misunderstandings disappear.

Now, is your requirement as listed in post #14 your final request? The reason I ask this is because XLD has already tried to provide a vba solution based on a somewhat unclear explanation offered in post #1, which turns out not to be what you were actually chasing. Please don't waste peoples time and effort.

[Rakesh]

Yes my requirement is finally listed in post #14.

Sorry for miscommunication. I used the word "Stub" as per my work area KeyWord.

[mbarron]

Based on post #14, I believe this does what you want.
Sub moveIt()
Dim rColA As Range, sMstr As Worksheet, sNew As Worksheet
Dim i As Integer, j As Integer
Dim lCol As Integer
Application.ScreenUpdating = False
Set sMstr = Sheets(1)
Set rColA = sMstr.Range("A1:A10")
lCol = sMstr.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 2 To lCol Step 5
j = j + 1
Set sNew = Sheets.Add(after:=Sheets(Sheets.Count))
sNew.Name = "Output " & Format(j, "00")
rColA.Copy
sNew.Cells(1, 1).PasteSpecial xlPasteAll
Range(sMstr.Cells(1, i), sMstr.Cells(10, i + 4)).Copy Destination:=sNew.Cells(1, 2)
sNew.Cells.Columns.AutoFit
sNew.Range("a1").Select
Next
sMstr.Activate
sMstr.Range("a1").Select
Application.ScreenUpdating = True
End Sub

[Rakesh]

Its working fine.

Thank You So much Guys...

[Rakesh]

Hi,

Some more help needed from you Guys.
How to get user input (i.e., Columns) and to split the table according to the User Inputs.

[mbarron]

Can you explain what you mean? What kind of input do you require from the user?

[Rakesh]

User Input should be Column Count.

If user input is No. 6 the table should be splitted as

Column A and Column B to Column G

If user input is No. 6 the table should be splitted as

Column A and Column B to Column I

Thanks

[mbarron]

Try this:

Sub moveIt()
Dim rColA As Range, sMstr As Worksheet, sNew As Worksheet
Dim iCCount As Integer
Dim i As Integer, j As Integer
Dim lCol As Integer
Application.ScreenUpdating = False
Set sMstr = Sheets(1)
Set rColA = sMstr.Range("A1:A10")
lCol = sMstr.Cells(1, Columns.Count).End(xlToLeft).Column
iCCount = Application.InputBox("Number of Columns", "Columns Please", Type:=1)
For i = 2 To lCol Step iCCount
j = j + 1
Set sNew = Sheets.Add(after:=Sheets(Sheets.Count))
sNew.Name = "Output " & Format(j, "00")
rColA.Copy
sNew.Cells(1, 1).PasteSpecial xlPasteAll
Range(sMstr.Cells(1, i), sMstr.Cells(10, i + iCCount - 1)).Copy Destination:=sNew.Cells(1, 2)
sNew.Cells.Columns.AutoFit
sNew.Range("a1").Select
Next
sMstr.Activate
sMstr.Range("a1").Select
Application.ScreenUpdating = True
End Sub

[Aussiebear]

User Input should be Column Count.

If user input is No. 6 the table should be splitted as

Column A and Column B to Column G

If user input is No. 6 the table should be splitted as

Column A and Column B to Column I

Thanks

Same input but two different outcomes???