order number

[Guest]

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

 

[Tom Ogilvy]

Something like:

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

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

 

[Guest]

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:

 

[Tom Ogilvy]

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

 

[Guest]

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:

 

[Tom Ogilvy]

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.

 

[Guest]

Hi Tom
It's the same
error on closing
but why didn't the code you write to me work?

Here i get no error but nothing happens



Alvin


"Tom Ogilvy" skrev:

 

[Tom Ogilvy]

You said it both causes an error and doesn't cause an error.

but why didn't the code you write to me work?

I am not sure you have established a creditable case that it didn't work.