order number

[Alvin Hansen]

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

--
Regards,
Tom Ogilvy



"Alvin Hansen" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Alvin Hansen]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

[Alvin Hansen]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Alvin Hansen]

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:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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]

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.



--
Regards,
Tom Ogilvy



"Alvin Hansen" <[email protected]> wrote in message
news:[email protected]...
> 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:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
> > message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >