Seamus2626
asked on
if VBA
Hi,
My below snippet of code copys data from sheets and pastes into another workbook.
I want to add a line to say
if sheet = "sheet1" or "Control" do not copy - basically skip to the next sheet
Thanks
Seamus
My below snippet of code copys data from sheets and pastes into another workbook.
I want to add a line to say
if sheet = "sheet1" or "Control" do not copy - basically skip to the next sheet
Thanks
Seamus
Sub SaveShtsAsBook_Sector_Booked()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
' Sheets("Sheet1").Select
' ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Select
ActiveSheet.ShowAllData
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Open Filename:= _
""
'Workbooks.Add (xlWBATWorksheet)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
recommendations
1. use direct assignment of values rather than the clipboard
Example in my article: https://www.experts-exchange.com/articles/2253/Fast-Data-Push-to-Excel.html
2. If you use direct assignment, you do not need to activate the worksheets
3. I'm a proponent of For Each...Next loops when iterating objects as shown in Rgonzo's code.
Alternative exclusion code examples:
1. use direct assignment of values rather than the clipboard
Example in my article: https://www.experts-exchange.com/articles/2253/Fast-Data-Push-to-Excel.html
2. If you use direct assignment, you do not need to activate the worksheets
3. I'm a proponent of For Each...Next loops when iterating objects as shown in Rgonzo's code.
Alternative exclusion code examples:
If sh.Name = "Sheet1" Or sh.Name = "Control" Then
Else
'data movement code
End If
'may be less reliable
If Instr(1, "Sheet1^Control", sh.Name, vbTextCompare) <> 0 Then
'data movement code
End If
'more reliable than above example
If Instr(1, "^Sheet1^Control^", "^" & sh.Name & "^", vbTextCompare) <> 0 Then
'data movement code
End If
Select Case UCase(sh.Name)
Case "SHEET1", "CONTROL"
'data movement code
End Select
Dim oDic As Object
Set oDic = CreateObject("Scripting.Dictionary")
oDic.CompareMode = 1 '1=vBTextCompare
oDic("Sheet1") = 1
oDic("Control") = 1
If oDic.Exists(sh.Name) Then
Else
'data movement code
End Select
ASKER
Thanks for the adds aikimark!!
Thanks for the adds aikimark!!You're welcome. When closing your questions, you can select multiple comments if more than one is helpful.
ASKER