Hope some one has a good idea and can help

I have a workbook there only have one sheet in this sheet i have a order
number
what i do now is copy this sheet into my open workbook then i take the number
and make a new number like number+1 its working all right but now i want to
copy the sheet back again so the new number come to the original sheet

Or maybe there is a nother way ??

The only thing i want to do is to see the number and change it with number+1

regards alvin

Something like:

with Activesheet.Range("A1")
.Value = .Value + 1
num = .Value
End With

workbooks("Something.xls").worksheets(1) _
.Range("A1").Value = num

--
Regards,
Tom Ogilvy



"Alvin Hansen" wrote in message
...
Hope some one has a good idea and can help

I have a workbook there only have one sheet in this sheet i have a order
number
what i do now is copy this sheet into my open workbook then i take the
number
and make a new number like number+1 its working all right but now i want
to
copy the sheet back again so the new number come to the original sheet

Or maybe there is a nother way ??

The only thing i want to do is to see the number and change it with
number+1

regards alvin

Hi Tom
Thanks
But I get an error her:
Workbooks("h:\city breaks\priser\usa\Fil.xls").fil _
.Range("A2").Value = num


error: subscript out of range.

Alvin


"Tom Ogilvy" skrev:

Something like:

with Activesheet.Range("A1")
.Value = .Value + 1
num = .Value
End With

workbooks("Something.xls").worksheets(1) _
.Range("A1").Value = num

--
Regards,
Tom Ogilvy



"Alvin Hansen" wrote in message
...
Hope some one has a good idea and can help

I have a workbook there only have one sheet in this sheet i have a order
number
what i do now is copy this sheet into my open workbook then i take the
number
and make a new number like number+1 its working all right but now i want
to
copy the sheet back again so the new number come to the original sheet

Or maybe there is a nother way ??

The only thing i want to do is to see the number and change it with
number+1

regards alvin

Only open workbooks are in the workbooks collection

Application.ScreenUpdating = False
set bk = Workbooks.Oen("h:\city breaks\priser\usa\Fil.xls")
bk.Worksheets(1).Range("A2").Value = num
bk.close Savechanges = True
Application.ScreenUpdating = True

--
Regards,
Tom Ogilvy


"Alvin Hansen" wrote in message
...
Hi Tom
Thanks
But I get an error her:
Workbooks("h:\city breaks\priser\usa\Fil.xls").fil _
.Range("A2").Value = num


error: subscript out of range.

Alvin


"Tom Ogilvy" skrev:

Something like:

with Activesheet.Range("A1")
.Value = .Value + 1
num = .Value
End With

workbooks("Something.xls").worksheets(1) _
.Range("A1").Value = num

--
Regards,
Tom Ogilvy



"Alvin Hansen" wrote in message
...
Hope some one has a good idea and can help

I have a workbook there only have one sheet in this sheet i have a
order
number
what i do now is copy this sheet into my open workbook then i take the
number
and make a new number like number+1 its working all right but now i
want
to
copy the sheet back again so the new number come to the original sheet

Or maybe there is a nother way ??

The only thing i want to do is to see the number and change it with
number+1

regards alvin

Hi tom
Well nothing happens
I have try and try and try
i ahve try
bk.Worksheets(1).Range("A2").Value = "bla bla"

I have try
bk.Worksheets("fil").Range("A2").Value = "0125"

but nothing happens
But i have this code :
Sub Copyrange1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "h:\city breaks\priser\usa\"
ChDrive MyPath
ChDir MyPath
FNames = Dir("fil.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook

Set mybook = Workbooks.Open(FNames)

Set sourceRange = basebook.Worksheets("fil").Range("a1:c5")
Set destrange = mybook.Worksheets(1).Range("a1")
sourceRange.copy destrange
' Instead of this lines you can use the code below to copy only the
values

' Set sourceRange = basebook.Worksheets(1).Range("a1:c5")
' Set destrange = mybook.Worksheets(1).Range("a1:c5")
' destrange.Value = sourceRange.Value

mybook.Close True
FNames = Dir()
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Its working if i run it from a userform i have
but if i take this userform to start in Workbook Open event
then i get an error
It can not close the workbook
in this line mybook.Close True
and the workbook file is open.

MAybe you have a good soluotion ??

I give up

Regards alvin






"Tom Ogilvy" skrev:

Only open workbooks are in the workbooks collection

Application.ScreenUpdating = False
set bk = Workbooks.Oen("h:\city breaks\priser\usa\Fil.xls")
bk.Worksheets(1).Range("A2").Value = num
bk.close Savechanges = True
Application.ScreenUpdating = True

--
Regards,
Tom Ogilvy


"Alvin Hansen" wrote in message
...
Hi Tom
Thanks
But I get an error her:
Workbooks("h:\city breaks\priser\usa\Fil.xls").fil _
.Range("A2").Value = num


error: subscript out of range.

Alvin


"Tom Ogilvy" skrev:

Something like:

with Activesheet.Range("A1")
.Value = .Value + 1
num = .Value
End With

workbooks("Something.xls").worksheets(1) _
.Range("A1").Value = num

--
Regards,
Tom Ogilvy



"Alvin Hansen" wrote in message
...
Hope some one has a good idea and can help

I have a workbook there only have one sheet in this sheet i have a
order
number
what i do now is copy this sheet into my open workbook then i take the
number
and make a new number like number+1 its working all right but now i
want
to
copy the sheet back again so the new number come to the original sheet

Or maybe there is a nother way ??

The only thing i want to do is to see the number and change it with
number+1

regards alvin

Private Sub Workbook_Open()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "h:\city breaks\priser\usa\"
ChDrive MyPath
ChDir MyPath
FNames = Dir("fil.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = Application.ThisWorkbook
Set mybook = Application.Workbooks.Open(FNames)
Set sourceRange = basebook.Worksheets("fil").Range("a1:c5")
Set destrange = mybook.Worksheets(1).Range("a1")
sourceRange.copy destrange
mybook.Close True
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Try that in your workbook_open event.

--
Regards,
Tom Ogilvy

"Alvin Hansen" wrote in message
...
Hi tom
Well nothing happens
I have try and try and try
i ahve try
bk.Worksheets(1).Range("A2").Value = "bla bla"

I have try
bk.Worksheets("fil").Range("A2").Value = "0125"

but nothing happens
But i have this code :
Sub Copyrange1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "h:\city breaks\priser\usa\"
ChDrive MyPath
ChDir MyPath
FNames = Dir("fil.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook

Set mybook = Workbooks.Open(FNames)

Set sourceRange = basebook.Worksheets("fil").Range("a1:c5")
Set destrange = mybook.Worksheets(1).Range("a1")
sourceRange.copy destrange
' Instead of this lines you can use the code below to copy only
the
values

' Set sourceRange = basebook.Worksheets(1).Range("a1:c5")
' Set destrange = mybook.Worksheets(1).Range("a1:c5")
' destrange.Value = sourceRange.Value

mybook.Close True
FNames = Dir()
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Its working if i run it from a userform i have
but if i take this userform to start in Workbook Open event
then i get an error
It can not close the workbook
in this line mybook.Close True
and the workbook file is open.

MAybe you have a good soluotion ??

I give up

Regards alvin






"Tom Ogilvy" skrev:

Only open workbooks are in the workbooks collection

Application.ScreenUpdating = False
set bk = Workbooks.Oen("h:\city breaks\priser\usa\Fil.xls")
bk.Worksheets(1).Range("A2").Value = num
bk.close Savechanges = True
Application.ScreenUpdating = True

--
Regards,
Tom Ogilvy


"Alvin Hansen" wrote in message
...
Hi Tom
Thanks
But I get an error her:
Workbooks("h:\city breaks\priser\usa\Fil.xls").fil _
.Range("A2").Value = num


error: subscript out of range.

Alvin


"Tom Ogilvy" skrev:

Something like:

with Activesheet.Range("A1")
.Value = .Value + 1
num = .Value
End With

workbooks("Something.xls").worksheets(1) _
.Range("A1").Value = num

--
Regards,
Tom Ogilvy



"Alvin Hansen" wrote in
message
...
Hope some one has a good idea and can help

I have a workbook there only have one sheet in this sheet i have a
order
number
what i do now is copy this sheet into my open workbook then i take
the
number
and make a new number like number+1 its working all right but now
i
want
to
copy the sheet back again so the new number come to the original
sheet

Or maybe there is a nother way ??

The only thing i want to do is to see the number and change it
with
number+1

regards alvin