Copying Worksheet to Another Open Workbook
 

No - and I just changed my code to match what you gave me still same
error.
The idea behind this code is that I'm comparing two arrays and pulling
out information
from both - if they match, then I need a new workbook and it needs to
have a worksheet
from the file with the code in it copied and moved into the new
workbook.

Here's a more complete set:

Sub MySubroutine()
Dim wsh As Object
Dim fs As Object
Dim DesktopPath As String
Dim DirString As String
Dim wb As Workbook
Set wsh = CreateObject("Wscript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DesktopPath = wsh.SpecialFolders.item("Desktop")
DirString = DesktopPath & "\Mynewfolder"
If UBound(item1and2)*Ubound(item3) 0 Then
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
Application.ScreenUpdating = False
Match = 0
Newitem1 = 1
Newitem2 = 1
Newitem3 = 1
For i = 1 to UBound(myfullArray,1)
For j = 1 to UBound(myselectionsArray)
If Left(myfullArray(i,1),2) & Mid(myfullArray(i,1),4,6) =
myselectionsArray(j) Then
Match = 1
If Newitem1 = 1 Then
fStr = filenamelist(Mid(myfullarry(i,1),1,2)-10) & ".xls"
fname = DirString & "\" fStr
Workbooks.Add xlWBATWorksheet
Set wb = ActiveWorkbook
With wb
..SaveAs fname
..Close False
End With
Workbooks("Myfile.xls").Worksheets("MyWorksheet"). Copy
After:=Workbooks(fStr).Sheets(1)
Workbooks(fStr).Activate
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
..
..
..
more code

Copying Worksheet to Another Open Workbook
 

I think you should start by putting:

Option Explicit

At the top of your module. This will force you to declare your variables and
it'll help you find typos:

In these two lines, it looks like you meant the same stuff, but spelled
myfullarray/myfullarry differently:


If Left(myfullArray(i,1),2) & Mid(myfullArray(i,1),4,6) =
----
and
----
fStr = filenamelist(Mid(myfullarry(i,1),1,2)-10) & ".xls"

But I really couldn't test much more. There were too many variables set
somewhere else.

" wrote:

No - and I just changed my code to match what you gave me still same
error.
The idea behind this code is that I'm comparing two arrays and pulling
out information
from both - if they match, then I need a new workbook and it needs to
have a worksheet
from the file with the code in it copied and moved into the new
workbook.

Here's a more complete set:

Sub MySubroutine()
Dim wsh As Object
Dim fs As Object
Dim DesktopPath As String
Dim DirString As String
Dim wb As Workbook
Set wsh = CreateObject("Wscript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DesktopPath = wsh.SpecialFolders.item("Desktop")
DirString = DesktopPath & "\Mynewfolder"
If UBound(item1and2)*Ubound(item3) 0 Then
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
Application.ScreenUpdating = False
Match = 0
Newitem1 = 1
Newitem2 = 1
Newitem3 = 1
For i = 1 to UBound(myfullArray,1)
For j = 1 to UBound(myselectionsArray)
If Left(myfullArray(i,1),2) & Mid(myfullArray(i,1),4,6) =
myselectionsArray(j) Then
Match = 1
If Newitem1 = 1 Then
fStr = filenamelist(Mid(myfullarry(i,1),1,2)-10) & ".xls"
fname = DirString & "\" fStr
Workbooks.Add xlWBATWorksheet
Set wb = ActiveWorkbook
With wb
.SaveAs fname
.Close False
End With
Workbooks("Myfile.xls").Worksheets("MyWorksheet"). Copy
After:=Workbooks(fStr).Sheets(1)
Workbooks(fStr).Activate
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
.
.
.
more code

--

Dave Peterson

Copying Worksheet to Another Open Workbook
 

Hi Marston,

In your code are you not closing th fStr workbook immediately prior to your
Temp sheet copy instruction?

fname = DirString & "\" fStr
Workbooks.Add xlWBATWorksheet
Set wb = ActiveWorkbook
With wb
.SaveAs fname
.Close False
End With
Workbooks("Myfile.xls").Worksheets("MyWorksheet"). Copy

---
Regards,
Norman



"Dave Peterson" wrote in message
...
I think you should start by putting:

Option Explicit

At the top of your module. This will force you to declare your variables
and
it'll help you find typos:

In these two lines, it looks like you meant the same stuff, but spelled
myfullarray/myfullarry differently:


If Left(myfullArray(i,1),2) & Mid(myfullArray(i,1),4,6) =
----
and
----
fStr = filenamelist(Mid(myfullarry(i,1),1,2)-10) & ".xls"

But I really couldn't test much more. There were too many variables set
somewhere else.

" wrote:

No - and I just changed my code to match what you gave me still same
error.
The idea behind this code is that I'm comparing two arrays and pulling
out information
from both - if they match, then I need a new workbook and it needs to
have a worksheet
from the file with the code in it copied and moved into the new
workbook.

Here's a more complete set:

Sub MySubroutine()
Dim wsh As Object
Dim fs As Object
Dim DesktopPath As String
Dim DirString As String
Dim wb As Workbook
Set wsh = CreateObject("Wscript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DesktopPath = wsh.SpecialFolders.item("Desktop")
DirString = DesktopPath & "\Mynewfolder"
If UBound(item1and2)*Ubound(item3) 0 Then
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
Application.ScreenUpdating = False
Match = 0
Newitem1 = 1
Newitem2 = 1
Newitem3 = 1
For i = 1 to UBound(myfullArray,1)
For j = 1 to UBound(myselectionsArray)
If Left(myfullArray(i,1),2) & Mid(myfullArray(i,1),4,6) =
myselectionsArray(j) Then
Match = 1
If Newitem1 = 1 Then
fStr = filenamelist(Mid(myfullarry(i,1),1,2)-10) & ".xls"
fname = DirString & "\" fStr
Workbooks.Add xlWBATWorksheet
Set wb = ActiveWorkbook
With wb
.SaveAs fname
.Close False
End With
Workbooks("Myfile.xls").Worksheets("MyWorksheet"). Copy
After:=Workbooks(fStr).Sheets(1)
Workbooks(fStr).Activate
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
.
.
.
more code

--

Dave Peterson