Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening new worksheets based on entries
Hi all,
Got another question about the macro I'm writing. It's an IP address database, the IPs are all listed in column A as such: 111.222.32.4 111.222.32.5 111.222.32.66 111.222.37.1 111.222.37.2 111.222.37.3 111.222.38.13 111.222.38.24 111.222.38.35 They are class B adresses, and the 3rd octet changes about 220 times throughout the text files (here I show it change from 32 to 37 to 38). I need to implement some steps which would open a new worksheet for every new 3rd octet in the text file. I know I will end up with a couple hundred sheets, but it's what my boss wants. Any ideas are more than welcome. Thanks in advance, Roman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening new worksheets based on entries
Assuming the IP's are column 1 and sorted on the 3rd octet:
Sub copyIP() Dim sh As Worksheet, Startrow As Long Dim lastrow As Long, v1, v, i As Long Set sh = Worksheets("Data") Startrow = 1 lastrow = sh.Cells(Rows.Count, 1).End(xlUp).Row ReDim v1(0 To 3) v1(2) = "" i = 1 For i = 1 To lastrow v = Split(sh.Cells(i, 1), ".") If v(2) < v1(2) And i < 1 Then Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = v1(2) sh.Range(sh.Rows(Startrow), sh.Rows(i - 1)).Copy _ ActiveSheet.Range("A1") Startrow = i End If v1 = v Next i Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = v1(2) sh.Range(sh.Rows(Startrow), sh.Rows(lastrow)).Copy _ ActiveSheet.Range("A1") End Sub -- Regards, Tom Ogilvy "RomanR" wrote: Hi all, Got another question about the macro I'm writing. It's an IP address database, the IPs are all listed in column A as such: 111.222.32.4 111.222.32.5 111.222.32.66 111.222.37.1 111.222.37.2 111.222.37.3 111.222.38.13 111.222.38.24 111.222.38.35 They are class B adresses, and the 3rd octet changes about 220 times throughout the text files (here I show it change from 32 to 37 to 38). I need to implement some steps which would open a new worksheet for every new 3rd octet in the text file. I know I will end up with a couple hundred sheets, but it's what my boss wants. Any ideas are more than welcome. Thanks in advance, Roman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening new worksheets based on entries
Thanks Tom. This line returns a run-time "subscript out of range" error
however: Set sh = Worksheets("Data") Thanks Roman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening new worksheets based on entries
I guess I should have said to change Data to reflect the name of the sheet
that contains your data, but I foolishly thought that would be obvious. My Bad Let me try again: The following code is tested and worked with the data you showed and the assumptions I previously stated. Make sure you change the sheet reference (worksheets("Data")) to refer to the sheet that contains your data. I placed your sample data on a sheet named DATA. Sub copyIP() Dim sh As Worksheet, Startrow As Long Dim lastrow As Long, v1, v, i As Long ' change the next line to reflect the sheet that contains ' your data. for example, if your data was on a sheet ' with the name sheet1, the next line would actually be ' Set sh = Worksheets("Sheet1") ' I used the name Data for illustration Set sh = Worksheets("Data") '<=== CHANGE Startrow = 1 lastrow = sh.Cells(Rows.Count, 1).End(xlUp).Row ReDim v1(0 To 3) v1(2) = "" i = 1 For i = 1 To lastrow v = Split(sh.Cells(i, 1), ".") If v(2) < v1(2) And i < 1 Then Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = v1(2) sh.Range(sh.Rows(Startrow), sh.Rows(i - 1)).Copy _ ActiveSheet.Range("A1") Startrow = i End If v1 = v Next i Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = v1(2) sh.Range(sh.Rows(Startrow), sh.Rows(lastrow)).Copy _ ActiveSheet.Range("A1") End Sub -- Regards, Tom Ogilvy "RomanR" wrote: Thanks Tom. This line returns a run-time "subscript out of range" error however: Set sh = Worksheets("Data") Thanks Roman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening new worksheets based on entries
It's my fault Tom, don't worry, it is obvious that it has to change. I
figured I had to use the name of my excel file, which I tried, except I entered the file name (dns.txt) instead of just the sheet name (dns). That's where I got confused and posted back here. It works wonders now, thanks very much again for the help, I appreciate it. Regards, Roman Tom Ogilvy wrote: I guess I should have said to change Data to reflect the name of the sheet that contains your data, but I foolishly thought that would be obvious. My Bad Let me try again: The following code is tested and worked with the data you showed and the assumptions I previously stated. Make sure you change the sheet reference (worksheets("Data")) to refer to the sheet that contains your data. I placed your sample data on a sheet named DATA. Sub copyIP() Dim sh As Worksheet, Startrow As Long Dim lastrow As Long, v1, v, i As Long ' change the next line to reflect the sheet that contains ' your data. for example, if your data was on a sheet ' with the name sheet1, the next line would actually be ' Set sh = Worksheets("Sheet1") ' I used the name Data for illustration Set sh = Worksheets("Data") '<=== CHANGE Startrow = 1 lastrow = sh.Cells(Rows.Count, 1).End(xlUp).Row ReDim v1(0 To 3) v1(2) = "" i = 1 For i = 1 To lastrow v = Split(sh.Cells(i, 1), ".") If v(2) < v1(2) And i < 1 Then Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = v1(2) sh.Range(sh.Rows(Startrow), sh.Rows(i - 1)).Copy _ ActiveSheet.Range("A1") Startrow = i End If v1 = v Next i Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = v1(2) sh.Range(sh.Rows(Startrow), sh.Rows(lastrow)).Copy _ ActiveSheet.Range("A1") End Sub -- Regards, Tom Ogilvy "RomanR" wrote: Thanks Tom. This line returns a run-time "subscript out of range" error however: Set sh = Worksheets("Data") Thanks Roman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening new worksheets based on entries
Glad you got it going. And don't mind me; I am not a butt all the time. I
was just playing with your a little <g -- Regards, Tom Ogilvy "RomanR" wrote: It's my fault Tom, don't worry, it is obvious that it has to change. I figured I had to use the name of my excel file, which I tried, except I entered the file name (dns.txt) instead of just the sheet name (dns). That's where I got confused and posted back here. It works wonders now, thanks very much again for the help, I appreciate it. Regards, Roman Tom Ogilvy wrote: I guess I should have said to change Data to reflect the name of the sheet that contains your data, but I foolishly thought that would be obvious. My Bad Let me try again: The following code is tested and worked with the data you showed and the assumptions I previously stated. Make sure you change the sheet reference (worksheets("Data")) to refer to the sheet that contains your data. I placed your sample data on a sheet named DATA. Sub copyIP() Dim sh As Worksheet, Startrow As Long Dim lastrow As Long, v1, v, i As Long ' change the next line to reflect the sheet that contains ' your data. for example, if your data was on a sheet ' with the name sheet1, the next line would actually be ' Set sh = Worksheets("Sheet1") ' I used the name Data for illustration Set sh = Worksheets("Data") '<=== CHANGE Startrow = 1 lastrow = sh.Cells(Rows.Count, 1).End(xlUp).Row ReDim v1(0 To 3) v1(2) = "" i = 1 For i = 1 To lastrow v = Split(sh.Cells(i, 1), ".") If v(2) < v1(2) And i < 1 Then Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = v1(2) sh.Range(sh.Rows(Startrow), sh.Rows(i - 1)).Copy _ ActiveSheet.Range("A1") Startrow = i End If v1 = v Next i Worksheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = v1(2) sh.Range(sh.Rows(Startrow), sh.Rows(lastrow)).Copy _ ActiveSheet.Range("A1") End Sub -- Regards, Tom Ogilvy "RomanR" wrote: Thanks Tom. This line returns a run-time "subscript out of range" error however: Set sh = Worksheets("Data") Thanks Roman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can cell entries be based on word entries in another cell ? | Excel Worksheet Functions | |||
Sum log entries based on date | Excel Discussion (Misc queries) | |||
Sum log entries based on date | Excel Discussion (Misc queries) | |||
advanced filtering based on entries ABOVE those I wish to keep | Excel Discussion (Misc queries) | |||
Duplicate entries in multiple worksheets | Excel Worksheet Functions |