Here is a tidied up version of your code, but I cannot see where other
workbooks or textboxes come into it, other than the initial textbox.

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim myVar As String
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

myVar = TextBox3.Text 'name of file entered by user
'YES, itshould it be the entire path
On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Overall Totals").Name) = 0 Then

Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Overall Totals"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)
sh.Range(sh.Rows(5), sh.Rows(shLast)).Copy _
DestSh.Cells(Last + 1, "A")
End If
Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "Delete the current overall totals and then repopulate"
End If

End Sub


--
HTH

Bob Phillips

"tonyDeBrasco" <tonyDeBrasco.1skpry@ wrote in message
...

thanks for that... I assume I must already have myVar defined as
something..

So the overall thing I want to achieve is being able to copy data from
lots of spreadsheets and each of these sheets is in an unopened
workbook. Then all this data will be combined to form an overall
totals.

if my thinking is correct hopefully something like this would at least
start what im trying to do, if anybody can take my fragmented code and
run with it a bit i'd be most grateful. As I have the form set up the
only function button that I have to get working is the "Generate
Totals" (CommandButton1_Click()) button...

Also as I am new to Excel and VB I will attempt to provide comments
showing what I think all of the lines mean, if I am wrong please
correct

Private Sub CommandButton1_Click()

Dim sh As Worksheet //new variable of type worksheet
Dim myVar As String //variable of type string
Dim DestSh As Worksheet
Dim shLast As Long //variable of type long
Dim Last As Long
myVar = TextBox3.Text //name of file entered by user - should it be
the entire path eg C:// etc...???
sh = myVar //assigning the text entered by user as the name of the
worksheet


On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Overall Totals").Name) = 0 Then
//check to see if sheet exists

Application.ScreenUpdating = False //don't know
Set DestSh = ThisWorkbook.Worksheets.Add //adding a sheet to the
current workbook
DestSh.Name = "Overall Totals" //naming that new sheet
For Each sh In ThisWorkbook.Worksheets //I think ThisWorkbook
should be myVar??
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh) //last row = function to find
last row
shLast = LastRow(sh) //so we know how many rows to
copy
sh.Range(sh.Rows(5), sh.Rows(shLast)).Copy
DestSh.Cells(Last + 1, "A") //copying the desired
End If
Next
DestSh.Cells(1).Select //don't know
Application.ScreenUpdating = True //don't know
Else
MsgBox "Delete the current overall totals and then repopulate"
End If

End Sub

I can't take credit for the code, it is compliments of Ron DeBruin bar
the bit at the top that doesn't work..

Hopefully someone can help...


--
tonyDeBrascoPosted from http://www.pcreview.co.uk/ newsgroup access