Macro problem...Will be a real challange
 

I have been given one excel sheet which is exported from some other site
(.csv file). It contains the data without any formatting. This data is
divided in some blocks. These blocks are differentiated with some blank
rows. Now I am suppose to copy one block (set of rows) in different
excel tab, 2nd in 2nd tab, 3rd in 3rd tab and so on...
I dont know how many blocks will be there. I just know that these
blocks are differentited by blank rows. All the data is in the first
column only.

Can anyone help me for this?


--
Sylvia
------------------------------------------------------------------------
Sylvia's Profile: http://www.excelforum.com/member.php...o&userid=30300
View this thread: http://www.excelforum.com/showthread...hreadid=500029

Macro problem...Will be a real challange
 

Sylvia,
Excel can open .csv files.
From there, record a macro of the steps you require; creating a new
worksheet, copying a block of data to this new WS.
You will need check on how to find the next blank row. Many examples on
Google
Loop through all the blocks, deciding when to stop because you have no more
data.

NickHK

"Sylvia" wrote in
message ...

I have been given one excel sheet which is exported from some other site
(.csv file). It contains the data without any formatting. This data is
divided in some blocks. These blocks are differentiated with some blank
rows. Now I am suppose to copy one block (set of rows) in different
excel tab, 2nd in 2nd tab, 3rd in 3rd tab and so on...
I dont know how many blocks will be there. I just know that these
blocks are differentited by blank rows. All the data is in the first
column only.

Can anyone help me for this?


--
Sylvia
------------------------------------------------------------------------
Sylvia's Profile:
http://www.excelforum.com/member.php...o&userid=30300
View this thread: http://www.excelforum.com/showthread...hreadid=500029

Macro problem...Will be a real challange
 

Thanks for your help. But how can I copy the data between two blank
rows? I mean whot will I specify in loop? Because depending upon that I
should generate the number of excel tabs.


--
Sylvia
------------------------------------------------------------------------
Sylvia's Profile: http://www.excelforum.com/member.php...o&userid=30300
View this thread: http://www.excelforum.com/showthread...hreadid=500029

Macro problem...Will be a real challange
 

Sylvia,
You should add error trapping:
<Code
Private Sub CommandButton1_Click()
Dim StartBlock As Range
Dim CopyBlock As Range
Dim NewWS As Worksheet

With ActiveSheet
'Assuming start is in A1
Set StartBlock = .Range("A1")
'If this blank, we need the first non-blank cell
If StartBlock.Value = "" Then Set StartBlock = .Range(StartBlock.Address
& ":" & StartBlock.End(xlDown).Address)

'Don't hard the Max Rows as Excel 12 etc will have 65K rows
Do Until StartBlock.Row = .Rows.Count

'Get the next block, before the next blank row
Set CopyBlock = .Range(StartBlock.Address & ":" &
StartBlock.End(xlDown).Address)

'Uncomment to see the Range that will be copied
'.Select
'CopyBlock.Select

'Create new WS at the end
Set NewWS = ThisWorkbook.Worksheets.Add(,
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Co unt))

'Assuming each block goes to A1
CopyBlock.Copy NewWS.Range("A1")

'Move StartBlock to the end of the CopyBlock
Set StartBlock = .Range(StartBlock.End(xlDown).Address)
'Now move to the start of the next block
Set StartBlock = .Range(StartBlock.End(xlDown).Address)
Loop
End With
End Sub
</Code

NickHK

"Sylvia" wrote in
message ...

Thanks for your help. But how can I copy the data between two blank
rows? I mean whot will I specify in loop? Because depending upon that I
should generate the number of excel tabs.


--
Sylvia
------------------------------------------------------------------------
Sylvia's Profile:
http://www.excelforum.com/member.php...o&userid=30300
View this thread: http://www.excelforum.com/showthread...hreadid=500029

Macro problem...Will be a real challange
 

Sylvia,
Slight correction

'Assuming start is in A1
Set StartBlock = .Range("A1")
'If this blank, we need the first non-blank cell
If StartBlock.Value = "" Then Set StartBlock =
..Range(StartBlock.End(xlDown).Address)

NickHK

"NickHK" wrote in message
...
Sylvia,
You should add error trapping:
<Code
Private Sub CommandButton1_Click()
Dim StartBlock As Range
Dim CopyBlock As Range
Dim NewWS As Worksheet

With ActiveSheet
'Assuming start is in A1
Set StartBlock = .Range("A1")
'If this blank, we need the first non-blank cell
If StartBlock.Value = "" Then Set StartBlock =
..Range(StartBlock.Address
& ":" & StartBlock.End(xlDown).Address)

'Don't hard the Max Rows as Excel 12 etc will have 65K rows
Do Until StartBlock.Row = .Rows.Count

'Get the next block, before the next blank row
Set CopyBlock = .Range(StartBlock.Address & ":" &
StartBlock.End(xlDown).Address)

'Uncomment to see the Range that will be copied
'.Select
'CopyBlock.Select

'Create new WS at the end
Set NewWS = ThisWorkbook.Worksheets.Add(,
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Co unt))

'Assuming each block goes to A1
CopyBlock.Copy NewWS.Range("A1")

'Move StartBlock to the end of the CopyBlock
Set StartBlock = .Range(StartBlock.End(xlDown).Address)
'Now move to the start of the next block
Set StartBlock = .Range(StartBlock.End(xlDown).Address)
Loop
End With
End Sub
</Code

NickHK

"Sylvia" wrote in
message ...

Thanks for your help. But how can I copy the data between two blank
rows? I mean whot will I specify in loop? Because depending upon that I
should generate the number of excel tabs.


--
Sylvia
------------------------------------------------------------------------
Sylvia's Profile:
http://www.excelforum.com/member.php...o&userid=30300
View this thread:
http://www.excelforum.com/showthread...hreadid=500029

Macro problem...Will be a real challange
 

Nick

Thank you sooooooooo much. It works perfectly.

Now only one thing is there. How rename the excel tab ass per th
blocks? I mean instead of Sheet1, sheet2... Block 1 and block 2...etc?

If I use this...
NewWS.Name = "Block?"
then what should be there at the place of "?"

Prach

--
Sylvi
-----------------------------------------------------------------------
Sylvia's Profile: http://www.excelforum.com/member.php...fo&userid=3030
View this thread: http://www.excelforum.com/showthread.php?threadid=50002

Macro problem...Will be a real challange
 

Sylvia,
Need to add a counter inside the loop:

Private Sub CommandButton1_Click()
Dim StartBlock As Range
Dim CopyBlock As Range
Dim NewWS As Worksheet
Dim BlockCount As Long '<<<<<<<Add

With ActiveSheet
'Assuming start is in A1
Set StartBlock = .Range("A1")
'If this blank, we need the first non-blank cell
If StartBlock.Value = "" Then Set StartBlock =
..Range(StartBlock.End(xlDown).Address)

Do Until StartBlock.Row = .Rows.Count

'Get the next block, before the next blank row
Set CopyBlock = .Range(StartBlock.Address & ":" &
StartBlock.End(xlDown).Address)

'Uncomment to see the Range that will be copied
'.Select
'CopyBlock.Select

'Create new WS at the end
Set NewWS = ThisWorkbook.Worksheets.Add(,
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Co unt))

BlockCount = BlockCount + 1 '<<<<<<<Add
NewWS.Name = "Block " & BlockCount '<<<<<<<Add

'Assuming each block goes to A1
CopyBlock.Copy NewWS.Range("A1")

'Move StartBlock to the end of the CopyBlock
Set StartBlock = .Range(StartBlock.End(xlDown).Address)
'Now move to the start of the next block
Set StartBlock = .Range(StartBlock.End(xlDown).Address)

Loop
End With
End Sub



"Sylvia" wrote in
message ...

Nick

Thank you sooooooooo much. It works perfectly.

Now only one thing is there. How rename the excel tab ass per the
blocks? I mean instead of Sheet1, sheet2... Block 1 and block 2...etc?

If I use this...
NewWS.Name = "Block?"
then what should be there at the place of "?"

Prachi


--
Sylvia
------------------------------------------------------------------------
Sylvia's Profile:
http://www.excelforum.com/member.php...o&userid=30300
View this thread: http://www.excelforum.com/showthread...hreadid=500029

Macro problem...Will be a real challange
 

Use

Dim nBlock As Long

nBlock = nBlock + 1
Worksheets.Add.Name = "Block" & nBlock

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sylvia" wrote in
message ...

Nick

Thank you sooooooooo much. It works perfectly.

Now only one thing is there. How rename the excel tab ass per the
blocks? I mean instead of Sheet1, sheet2... Block 1 and block 2...etc?

If I use this...
NewWS.Name = "Block?"
then what should be there at the place of "?"

Prachi


--
Sylvia
------------------------------------------------------------------------
Sylvia's Profile:
http://www.excelforum.com/member.php...o&userid=30300
View this thread: http://www.excelforum.com/showthread...hreadid=500029

Macro problem...Will be a real challange
 

Hi Nick,

Thank you very very much for your help. It works exactly as I wanted.

Sylvia :)

Thanks to u too Bob... :)


--
Sylvia
------------------------------------------------------------------------
Sylvia's Profile: http://www.excelforum.com/member.php...o&userid=30300
View this thread: http://www.excelforum.com/showthread...hreadid=500029