I have a macro that copies data from other workbooks. The problem is that the
data always displays with the $ sign when it should be the £ sign. The source
data is defined as £. when I go in the format, options and select currency
and £ it will not change the cell.

Most annoying, please help

Chris

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a macro that copies data from other workbooks. The problem is
that the
data always displays with the $ sign when it should be the £ sign.
The source
data is defined as £. when I go in the format, options and select
currency
and £ it will not change the cell.

Most annoying, please help

Chris

Frank
Here's a copy of the macro. WHat I'm trying to do is get the macro to prompt
for a workbook. Once selected copy the range A9:G29 of the worksheet Summary
and paste into a set range. Then loop back etc. so I'll build up one
worksheet with all the summary sheets from the other books

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim Mainloop As Long
Dim Clearcells As Long
Dim Cellstartlocation As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Cellstartlocation = 1
For Mainloop = 1 To 2
Clearcells = Clearcells + 1
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Rousec\My Documents\wickes"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
rum = 1

If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1

If Clearcells = 1 Then basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A9:G29")
SourceRcount = sourceRange.Rows.Count
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A")
With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A"). _
Resize(.Rows.Count,
..Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Cellstartlocation = Cellstartlocation + 30
Next
End If
Next
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub




"Frank Kabel" wrote:

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a macro that copies data from other workbooks. The problem is
that the
data always displays with the $ sign when it should be the £ sign.
The source
data is defined as £. when I go in the format, options and select
currency
and £ it will not change the cell.

Most annoying, please help

Chris

Did you verify that the values with dollar signs are really text?

If yes, you could select the range (a column or all the cells) and do:

Edit|replace
what: $ (dollar sign)
with: (leave blank)
replace all

If it's successful when you do it manually, record a macro and add that to the
bottom of your code.

Chris wrote:

Frank
Here's a copy of the macro. WHat I'm trying to do is get the macro to prompt
for a workbook. Once selected copy the range A9:G29 of the worksheet Summary
and paste into a set range. Then loop back etc. so I'll build up one
worksheet with all the summary sheets from the other books

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim Mainloop As Long
Dim Clearcells As Long
Dim Cellstartlocation As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Cellstartlocation = 1
For Mainloop = 1 To 2
Clearcells = Clearcells + 1
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Rousec\My Documents\wickes"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
rum = 1

If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1

If Clearcells = 1 Then basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A9:G29")
SourceRcount = sourceRange.Rows.Count
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A")
With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A"). _
Resize(.Rows.Count,
.Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Cellstartlocation = Cellstartlocation + 30
Next
End If
Next
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Frank Kabel" wrote:

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a macro that copies data from other workbooks. The problem is
that the
data always displays with the $ sign when it should be the £ sign.
The source
data is defined as £. when I go in the format, options and select
currency
and £ it will not change the cell.

Most annoying, please help

Chris



--

Dave Peterson

Thanks Dave
I will try this. In the mean time I replaced some code with the following
mybook.Activate
sourceRange.Select
Selection.Copy
basebook.Activate
destrange.Select
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False,
Transpose:=False

This works but I always get the Clipboard poping up saying do I want to save
the data to the clipboard Yes/No Cancel. Is there any way to surpress this
message?

Chris

"Dave Peterson" wrote:

Did you verify that the values with dollar signs are really text?

If yes, you could select the range (a column or all the cells) and do:

Edit|replace
what: $ (dollar sign)
with: (leave blank)
replace all

If it's successful when you do it manually, record a macro and add that to the
bottom of your code.

Chris wrote:

Frank
Here's a copy of the macro. WHat I'm trying to do is get the macro to prompt
for a workbook. Once selected copy the range A9:G29 of the worksheet Summary
and paste into a set range. Then loop back etc. so I'll build up one
worksheet with all the summary sheets from the other books

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim Mainloop As Long
Dim Clearcells As Long
Dim Cellstartlocation As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Cellstartlocation = 1
For Mainloop = 1 To 2
Clearcells = Clearcells + 1
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Rousec\My Documents\wickes"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
rum = 1

If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1

If Clearcells = 1 Then basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A9:G29")
SourceRcount = sourceRange.Rows.Count
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A")
With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A"). _
Resize(.Rows.Count,
.Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Cellstartlocation = Cellstartlocation + 30
Next
End If
Next
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Frank Kabel" wrote:

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a macro that copies data from other workbooks. The problem is
that the
data always displays with the $ sign when it should be the £ sign.
The source
data is defined as £. when I go in the format, options and select
currency
and £ it will not change the cell.

Most annoying, please help

Chris



--

Dave Peterson

Tried the Edit|Replace and it works, but leaves me with the drop down box
with the option convert to number. All cells have the little green flag on
them. I tried to create a macro that would then select the range and do the
convert to number but doesn't work, any ideas

Chris

"Chris" wrote:

Thanks Dave
I will try this. In the mean time I replaced some code with the following
mybook.Activate
sourceRange.Select
Selection.Copy
basebook.Activate
destrange.Select
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False,
Transpose:=False

This works but I always get the Clipboard poping up saying do I want to save
the data to the clipboard Yes/No Cancel. Is there any way to surpress this
message?

Chris

"Dave Peterson" wrote:

Did you verify that the values with dollar signs are really text?

If yes, you could select the range (a column or all the cells) and do:

Edit|replace
what: $ (dollar sign)
with: (leave blank)
replace all

If it's successful when you do it manually, record a macro and add that to the
bottom of your code.

Chris wrote:

Frank
Here's a copy of the macro. WHat I'm trying to do is get the macro to prompt
for a workbook. Once selected copy the range A9:G29 of the worksheet Summary
and paste into a set range. Then loop back etc. so I'll build up one
worksheet with all the summary sheets from the other books

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim Mainloop As Long
Dim Clearcells As Long
Dim Cellstartlocation As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Cellstartlocation = 1
For Mainloop = 1 To 2
Clearcells = Clearcells + 1
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Rousec\My Documents\wickes"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
rum = 1

If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1

If Clearcells = 1 Then basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A9:G29")
SourceRcount = sourceRange.Rows.Count
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A")
With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A"). _
Resize(.Rows.Count,
.Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Cellstartlocation = Cellstartlocation + 30
Next
End If
Next
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Frank Kabel" wrote:

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a macro that copies data from other workbooks. The problem is
that the
data always displays with the $ sign when it should be the £ sign.
The source
data is defined as £. when I go in the format, options and select
currency
and £ it will not change the cell.

Most annoying, please help

Chris



--

Dave Peterson

It sounds like the values are still text. Can you format the cells as General
first, then do the edit|replace?

(test manually as a quick check.)

For what it's worth, xl2002 (USA version), if I edit|replace some characters out
of a cell formatted as text and leave only numeric characters, then the value is
numeric.

Any chance you have a decimal point problem, too (comma vs dot)?

I'd guess no, since that green warning flag already showed up.



Chris wrote:

Tried the Edit|Replace and it works, but leaves me with the drop down box
with the option convert to number. All cells have the little green flag on
them. I tried to create a macro that would then select the range and do the
convert to number but doesn't work, any ideas

Chris

"Chris" wrote:

Thanks Dave
I will try this. In the mean time I replaced some code with the following
mybook.Activate
sourceRange.Select
Selection.Copy
basebook.Activate
destrange.Select
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False,
Transpose:=False

This works but I always get the Clipboard poping up saying do I want to save
the data to the clipboard Yes/No Cancel. Is there any way to surpress this
message?

Chris

"Dave Peterson" wrote:

Did you verify that the values with dollar signs are really text?

If yes, you could select the range (a column or all the cells) and do:

Edit|replace
what: $ (dollar sign)
with: (leave blank)
replace all

If it's successful when you do it manually, record a macro and add that to the
bottom of your code.

Chris wrote:

Frank
Here's a copy of the macro. WHat I'm trying to do is get the macro to prompt
for a workbook. Once selected copy the range A9:G29 of the worksheet Summary
and paste into a set range. Then loop back etc. so I'll build up one
worksheet with all the summary sheets from the other books

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim Mainloop As Long
Dim Clearcells As Long
Dim Cellstartlocation As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Cellstartlocation = 1
For Mainloop = 1 To 2
Clearcells = Clearcells + 1
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Rousec\My Documents\wickes"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
rum = 1

If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1

If Clearcells = 1 Then basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A9:G29")
SourceRcount = sourceRange.Rows.Count
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A")
With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A"). _
Resize(.Rows.Count,
.Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Cellstartlocation = Cellstartlocation + 30
Next
End If
Next
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Frank Kabel" wrote:

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a macro that copies data from other workbooks. The problem is
that the
data always displays with the $ sign when it should be the £ sign.
The source
data is defined as £. when I go in the format, options and select
currency
and £ it will not change the cell.

Most annoying, please help

Chris



--

Dave Peterson


--

Dave Peterson

What I've done is edit the macro to use Edit, Replace and then turn off the
error checking under Tools Options and the green flags disapear, may be a
liitle cheating but it works. I tried formatting the columns first but does
not work. Is it possible to turn of the Clipboard so it does not keep poping
up asking if you wish to keep data for later?

Chris

"Dave Peterson" wrote:

It sounds like the values are still text. Can you format the cells as General
first, then do the edit|replace?

(test manually as a quick check.)

For what it's worth, xl2002 (USA version), if I edit|replace some characters out
of a cell formatted as text and leave only numeric characters, then the value is
numeric.

Any chance you have a decimal point problem, too (comma vs dot)?

I'd guess no, since that green warning flag already showed up.



Chris wrote:

Tried the Edit|Replace and it works, but leaves me with the drop down box
with the option convert to number. All cells have the little green flag on
them. I tried to create a macro that would then select the range and do the
convert to number but doesn't work, any ideas

Chris

"Chris" wrote:

Thanks Dave
I will try this. In the mean time I replaced some code with the following
mybook.Activate
sourceRange.Select
Selection.Copy
basebook.Activate
destrange.Select
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False,
Transpose:=False

This works but I always get the Clipboard poping up saying do I want to save
the data to the clipboard Yes/No Cancel. Is there any way to surpress this
message?

Chris

"Dave Peterson" wrote:

Did you verify that the values with dollar signs are really text?

If yes, you could select the range (a column or all the cells) and do:

Edit|replace
what: $ (dollar sign)
with: (leave blank)
replace all

If it's successful when you do it manually, record a macro and add that to the
bottom of your code.

Chris wrote:

Frank
Here's a copy of the macro. WHat I'm trying to do is get the macro to prompt
for a workbook. Once selected copy the range A9:G29 of the worksheet Summary
and paste into a set range. Then loop back etc. so I'll build up one
worksheet with all the summary sheets from the other books

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim Mainloop As Long
Dim Clearcells As Long
Dim Cellstartlocation As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Cellstartlocation = 1
For Mainloop = 1 To 2
Clearcells = Clearcells + 1
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Rousec\My Documents\wickes"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
rum = 1

If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1

If Clearcells = 1 Then basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A9:G29")
SourceRcount = sourceRange.Rows.Count
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A")
With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A"). _
Resize(.Rows.Count,
.Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Cellstartlocation = Cellstartlocation + 30
Next
End If
Next
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Frank Kabel" wrote:

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a macro that copies data from other workbooks. The problem is
that the
data always displays with the $ sign when it should be the ÀšÃ‚£ sign.
The source
data is defined as ÀšÃ‚£. when I go in the format, options and select
currency
and ÀšÃ‚£ it will not change the cell.

Most annoying, please help

Chris



--

Dave Peterson


--

Dave Peterson

Application.cutcopymode = false

should help.

Chip Pearson has an addin that can show you what's in the cell (character by
character).
http://www.cpearson.com/excel/CellView.htm

If there's something weird in there, it might help you find it.



Chris wrote:

What I've done is edit the macro to use Edit, Replace and then turn off the
error checking under Tools Options and the green flags disapear, may be a
liitle cheating but it works. I tried formatting the columns first but does
not work. Is it possible to turn of the Clipboard so it does not keep poping
up asking if you wish to keep data for later?

Chris

"Dave Peterson" wrote:

It sounds like the values are still text. Can you format the cells as General
first, then do the edit|replace?

(test manually as a quick check.)

For what it's worth, xl2002 (USA version), if I edit|replace some characters out
of a cell formatted as text and leave only numeric characters, then the value is
numeric.

Any chance you have a decimal point problem, too (comma vs dot)?

I'd guess no, since that green warning flag already showed up.



Chris wrote:

Tried the Edit|Replace and it works, but leaves me with the drop down box
with the option convert to number. All cells have the little green flag on
them. I tried to create a macro that would then select the range and do the
convert to number but doesn't work, any ideas

Chris

"Chris" wrote:

Thanks Dave
I will try this. In the mean time I replaced some code with the following
mybook.Activate
sourceRange.Select
Selection.Copy
basebook.Activate
destrange.Select
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False,
Transpose:=False

This works but I always get the Clipboard poping up saying do I want to save
the data to the clipboard Yes/No Cancel. Is there any way to surpress this
message?

Chris

"Dave Peterson" wrote:

Did you verify that the values with dollar signs are really text?

If yes, you could select the range (a column or all the cells) and do:

Edit|replace
what: $ (dollar sign)
with: (leave blank)
replace all

If it's successful when you do it manually, record a macro and add that to the
bottom of your code.

Chris wrote:

Frank
Here's a copy of the macro. WHat I'm trying to do is get the macro to prompt
for a workbook. Once selected copy the range A9:G29 of the worksheet Summary
and paste into a set range. Then loop back etc. so I'll build up one
worksheet with all the summary sheets from the other books

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim Mainloop As Long
Dim Clearcells As Long
Dim Cellstartlocation As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Cellstartlocation = 1
For Mainloop = 1 To 2
Clearcells = Clearcells + 1
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Rousec\My Documents\wickes"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
rum = 1

If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1

If Clearcells = 1 Then basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A9:G29")
SourceRcount = sourceRange.Rows.Count
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A")
With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A"). _
Resize(.Rows.Count,
.Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Cellstartlocation = Cellstartlocation + 30
Next
End If
Next
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Frank Kabel" wrote:

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a macro that copies data from other workbooks. The problem is
that the
data always displays with the $ sign when it should be the ÀšÃ‚£ sign.
The source
data is defined as ÀšÃ‚£. when I go in the format, options and select
currency
and ÀšÃ‚£ it will not change the cell.

Most annoying, please help

Chris



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Thanks this works fine. I'll test my new macro on Excel 97 and see. People
with old unsupported software, what shall we do with them???

Chris

"Dave Peterson" wrote:

Application.cutcopymode = false

should help.

Chip Pearson has an addin that can show you what's in the cell (character by
character).
http://www.cpearson.com/excel/CellView.htm

If there's something weird in there, it might help you find it.



Chris wrote:

What I've done is edit the macro to use Edit, Replace and then turn off the
error checking under Tools Options and the green flags disapear, may be a
liitle cheating but it works. I tried formatting the columns first but does
not work. Is it possible to turn of the Clipboard so it does not keep poping
up asking if you wish to keep data for later?

Chris

"Dave Peterson" wrote:

It sounds like the values are still text. Can you format the cells as General
first, then do the edit|replace?

(test manually as a quick check.)

For what it's worth, xl2002 (USA version), if I edit|replace some characters out
of a cell formatted as text and leave only numeric characters, then the value is
numeric.

Any chance you have a decimal point problem, too (comma vs dot)?

I'd guess no, since that green warning flag already showed up.



Chris wrote:

Tried the Edit|Replace and it works, but leaves me with the drop down box
with the option convert to number. All cells have the little green flag on
them. I tried to create a macro that would then select the range and do the
convert to number but doesn't work, any ideas

Chris

"Chris" wrote:

Thanks Dave
I will try this. In the mean time I replaced some code with the following
mybook.Activate
sourceRange.Select
Selection.Copy
basebook.Activate
destrange.Select
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False,
Transpose:=False

This works but I always get the Clipboard poping up saying do I want to save
the data to the clipboard Yes/No Cancel. Is there any way to surpress this
message?

Chris

"Dave Peterson" wrote:

Did you verify that the values with dollar signs are really text?

If yes, you could select the range (a column or all the cells) and do:

Edit|replace
what: $ (dollar sign)
with: (leave blank)
replace all

If it's successful when you do it manually, record a macro and add that to the
bottom of your code.

Chris wrote:

Frank
Here's a copy of the macro. WHat I'm trying to do is get the macro to prompt
for a workbook. Once selected copy the range A9:G29 of the worksheet Summary
and paste into a set range. Then loop back etc. so I'll build up one
worksheet with all the summary sheets from the other books

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim Mainloop As Long
Dim Clearcells As Long
Dim Cellstartlocation As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Cellstartlocation = 1
For Mainloop = 1 To 2
Clearcells = Clearcells + 1
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Rousec\My Documents\wickes"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
rum = 1

If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1

If Clearcells = 1 Then basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A9:G29")
SourceRcount = sourceRange.Rows.Count
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A")
With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(Cellstartlocation, "A"). _
Resize(.Rows.Count,
.Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Cellstartlocation = Cellstartlocation + 30
Next
End If
Next
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Frank Kabel" wrote:

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a macro that copies data from other workbooks. The problem is
that the
data always displays with the $ sign when it should be the ÀšÃ€šÃ‚£ sign.
The source
data is defined as ÀšÃ€šÃ‚£. when I go in the format, options and select
currency
and ÀšÃ€šÃ‚£ it will not change the cell.

Most annoying, please help

Chris



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson