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