Macro problem...Will be a real challange

[Sylvia]

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?

 

[NickHK]

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]

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.

 

[NickHK]

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.Count))

'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

 

[NickHK]

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

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.Count))

'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]

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

 

[NickHK]

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.Count))

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

 

[Bob Phillips]

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]

Hi Nick,

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

Sylvia

Thanks to u too Bob...