Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macros
I have a macro that pulls in data from other workbooks but it always displays
the currency as dollars $ I want it to display as pounds £ I cannot change this even by using the format option once the data has been pulled. Any ideas? My macro is 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") 'basebook.Worksheets(1).Cells(rnum, "D").Value = mybook.Name ' This will add the workbook name in column D if you want 'sourceRange.Copy destrange ' Instead of this line you can use the code below to copy only the values 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 |
#2
|
|||
|
|||
Also Is there any way I can get the Macro to read the start and end of a loop
from an excel cell. And lastly is there any way I can get the macro to read a list of the workbooks from excel cells even if I have to name the range "Chris" wrote: I have a macro that pulls in data from other workbooks but it always displays the currency as dollars $ I want it to display as pounds £ I cannot change this even by using the format option once the data has been pulled. Any ideas? My macro is 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") 'basebook.Worksheets(1).Cells(rnum, "D").Value = mybook.Name ' This will add the workbook name in column D if you want 'sourceRange.Copy destrange ' Instead of this line you can use the code below to copy only the values 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros making file saving extremely slow. | Excel Discussion (Misc queries) | |||
Transferring toolbars and macros to other computers | Excel Discussion (Misc queries) | |||
How do I stop Excel from prompting to enable or disable macros? | Excel Discussion (Misc queries) | |||
The available macros list in XL; how to suppress filename from showing | Excel Discussion (Misc queries) | |||
Macros disappear after a file is imported | Excel Discussion (Misc queries) |