| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ignoring certain tab names
I have the following code to create a summary sheet looping through
multiple worksheets how do I add in lines to NOT include sheets named: data template 1 day moves 3 day moves Sub MakeSummary_1day() Dim J As Long Dim I As Long Dim Tab_Name As String Sheets.Add.Name = "1 day moves" Sheets("1 day moves").Select Range("$A$3:$EK$104").Value = "" J = 3 For I = 2 To Sheets.Count Range("A" + Format(J)).FormulaR1C1 = Tab_Name Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! R5C11" Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! R5C12" Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! R5C13" J = J + 1 Next I End Sub MANY THANKS |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ignoring certain tab names
Perhaps stating the sheets you want to ignore like this: Sub MakeSummary_1day() Dim J As Long Dim I As Long Dim Tab_Name As String Sheets.Add.Name = "1 day moves" Sheets("1 day moves").Select Range("$A$3:$EK$104").Value = "" J = 3 For I = 2 To Sheets.Count If Sheet.Name = "Sheet1" Or Sheet.Name = "Sheet2" Then GoTo Nxt Range("A" + Format(J)).FormulaR1C1 = Tab_Name Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C11 "" Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C12 "" Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C13 "" J = J + 1 Nxt: Next I End Sub Rob P;309469 Wrote: I have the following code to create a summary sheet looping through multiple worksheets how do I add in lines to NOT include sheets named: data template 1 day moves 3 day moves Code: -------------------- Sub MakeSummary_1day() Dim J As Long Dim I As Long Dim Tab_Name As String Sheets.Add.Name = "1 day moves" Sheets("1 day moves").Select Range("$A$3:$EK$104").Value = "" J = 3 For I = 2 To Sheets.Count Range("A" + Format(J)).FormulaR1C1 = Tab_Name Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! R5C11" Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! R5C12" Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! R5C13" J = J + 1 Next I End Sub -------------------- MANY THANKS -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86523 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ignoring certain tab names
On Apr 15, 3:08*pm, Simon Lloyd
wrote: Perhaps stating the sheets you want to ignore like this: Sub MakeSummary_1day() Dim J As Long Dim I As Long Dim Tab_Name As String Sheets.Add.Name = "1 day moves" Sheets("1 day moves").Select Range("$A$3:$EK$104").Value = "" J = 3 For I = 2 To Sheets.Count If Sheet.Name = "Sheet1" Or Sheet.Name = "Sheet2" Then GoTo Nxt Range("A" + Format(J)).FormulaR1C1 = Tab_Name Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C11 "" Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C12 "" Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C13 "" J = J + 1 Nxt: Next I End Sub Rob P;309469 Wrote: I have the following code to create a summary sheet looping through multiple worksheets how do I add in lines to NOT include sheets named: data template 1 day moves 3 day moves Code: -------------------- * * * * Sub MakeSummary_1day() * Dim J As Long * Dim I As Long * Dim Tab_Name As String * Sheets.Add.Name = "1 day moves" * Sheets("1 day moves").Select * Range("$A$3:$EK$104").Value = "" * J = 3 * For I = 2 To Sheets.Count * * Range("A" + Format(J)).FormulaR1C1 = Tab_Name * Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C11" * Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C12" * Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C13" * * * J = J + 1 * * Next I * * End Sub * -------------------- MANY THANKS -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=86523 I cannot get this to work... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ignoring certain tab names
In what sense? did you change the sheet names for your actual sheet names? did the code simply not run or did it give an error? or did it run without error but still process all tabs? Rob P;309490 Wrote: On Apr 15, 3:08*pm, Simon Lloyd wrote: Perhaps stating the sheets you want to ignore like this: Sub MakeSummary_1day() Dim J As Long Dim I As Long Dim Tab_Name As String Sheets.Add.Name = "1 day moves" Sheets("1 day moves").Select Range("$A$3:$EK$104").Value = "" J = 3 For I = 2 To Sheets.Count If Sheet.Name = "Sheet1" Or Sheet.Name = "Sheet2" Then GoTo Nxt Range("A" + Format(J)).FormulaR1C1 = Tab_Name Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C11 "" Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C12 "" Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C13 "" J = J + 1 Nxt: Next I End Sub Rob P;309469 Wrote: I have the following code to create a summary sheet looping through multiple worksheets how do I add in lines to NOT include sheets named: data template 1 day moves 3 day moves Code: -------------------- * * * * Sub MakeSummary_1day() * Dim J As Long * Dim I As Long * Dim Tab_Name As String * Sheets.Add.Name = "1 day moves" * Sheets("1 day moves").Select * Range("$A$3:$EK$104").Value = "" * J = 3 * For I = 2 To Sheets.Count * * Range("A" + Format(J)).FormulaR1C1 = Tab_Name * Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C11" * Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C12" * Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C13" * * * J = J + 1 * * Next I * * End Sub * -------------------- MANY THANKS -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread:'ignoring certain tab names - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86523) I cannot get this to work... -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86523 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ignoring certain tab names
On Apr 15, 3:16*pm, Rob P wrote:
On Apr 15, 3:08*pm, Simon Lloyd wrote: Perhaps stating the sheets you want to ignore like this: Sub MakeSummary_1day() Dim J As Long Dim I As Long Dim Tab_Name As String Sheets.Add.Name = "1 day moves" Sheets("1 day moves").Select Range("$A$3:$EK$104").Value = "" J = 3 For I = 2 To Sheets.Count If Sheet.Name = "Sheet1" Or Sheet.Name = "Sheet2" Then GoTo Nxt Range("A" + Format(J)).FormulaR1C1 = Tab_Name Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C11 "" Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C12 "" Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C13 "" J = J + 1 Nxt: Next I End Sub Rob P;309469 Wrote: I have the following code to create a summary sheet looping through multiple worksheets how do I add in lines to NOT include sheets named: data template 1 day moves 3 day moves Code: -------------------- * * * * Sub MakeSummary_1day() * Dim J As Long * Dim I As Long * Dim Tab_Name As String * Sheets.Add.Name = "1 day moves" * Sheets("1 day moves").Select * Range("$A$3:$EK$104").Value = "" * J = 3 * For I = 2 To Sheets.Count * * Range("A" + Format(J)).FormulaR1C1 = Tab_Name * Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C11" * Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C12" * Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C13" * * * J = J + 1 * * Next I * * End Sub * -------------------- MANY THANKS -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=86523 I cannot get this to work...- Hide quoted text - - Show quoted text - I used this to get it working: If Sheets(I).Name = "data" Or Sheets(I).Name = "1 day moves" Or Sheets (I).Name = "3 day moves" Then GoTo Nxt |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ignoring certain tab names
On Apr 15, 3:16*pm, Rob P wrote:
On Apr 15, 3:08*pm, Simon Lloyd wrote: Perhaps stating the sheets you want to ignore like this: Sub MakeSummary_1day() Dim J As Long Dim I As Long Dim Tab_Name As String Sheets.Add.Name = "1 day moves" Sheets("1 day moves").Select Range("$A$3:$EK$104").Value = "" J = 3 For I = 2 To Sheets.Count If Sheet.Name = "Sheet1" Or Sheet.Name = "Sheet2" Then GoTo Nxt Range("A" + Format(J)).FormulaR1C1 = Tab_Name Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C11 "" Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C12 "" Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'!" R5C13 "" J = J + 1 Nxt: Next I End Sub Rob P;309469 Wrote: I have the following code to create a summary sheet looping through multiple worksheets how do I add in lines to NOT include sheets named: data template 1 day moves 3 day moves Code: -------------------- * * * * Sub MakeSummary_1day() * Dim J As Long * Dim I As Long * Dim Tab_Name As String * Sheets.Add.Name = "1 day moves" * Sheets("1 day moves").Select * Range("$A$3:$EK$104").Value = "" * J = 3 * For I = 2 To Sheets.Count * * Range("A" + Format(J)).FormulaR1C1 = Tab_Name * Range("B" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C11" * Range("C" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C12" * Range("D" + Format(J)).FormulaR1C1 = "='" + Tab_Name + "'! * R5C13" * * * J = J + 1 * * Next I * * End Sub * -------------------- MANY THANKS -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=86523 I cannot get this to work...- Hide quoted text - - Show quoted text - If Sheets(I).Name = "data" Or Sheets(I).Name = "1 day moves" Or Sheets (I).Name = "3 day moves" Then GoTo Nxt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting Names in a Column, Ignoring Duplicates | Excel Discussion (Misc queries) | |||
| Ignoring #n/a | Excel Discussion (Misc queries) | |||
| Minimum value ignoring zero | Excel Programming | |||
| Ignoring 0 | Charts and Charting in Excel | |||
| Ignoring #DIV/0! | Excel Discussion (Misc queries) | |||
Linear Mode
