|
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 |
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 |
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... |
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 |
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 |
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 |
| All times are GMT +1. The time now is 01:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com