Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Real problem with SumProduct | Excel Worksheet Functions | |||
File Size a real problem! | Excel Discussion (Misc queries) | |||
averging prices real problem | Excel Programming | |||
averging prices real problem | Excel Worksheet Functions | |||
a challange for the "real" programmers | Excel Programming |