Spider_Lily
09-11-2008, 01:28 AM
Hi everybody,
i desperately need some help here. I need to create an Excel file that transfer data from a workbook which is the Form, to another workbook which is the Summary file.
Because the data is not in a continuous chain, the data need to copy and paste into the cells individually. This Summary file will be updated on an ongoing basis and will be used by several users. The Form and Summary will be in different Workbook when it is being used, but I can only attach one file for this forum so they are in the same workbook.
Below is the code that I have written but I think there are a lot of bugs as I can't run it.
There's alot of things that I'm not sure about the VBA code, like how to find the available row, how to set the destination file as the summary file and how should i write the VBA code. Should I use the "=" operator or should I use the copy method? There are still many questions on my mind but i'm quite lost here.:doh:
So any help is greatly appreciated!: pray2:
Sub MergeHorizontally()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim SourceCcount As Long, FNum As Long
Dim SourceWbk As Workbook, DestWbks As Workbook
Dim sourceRange As Range, destrange As Range
Dim Cnum As Long, CalcMode As Long
Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
'Set up list of file filters
Finfo = "Text files (*.txt),*.txt," & _
"All Files(*.*),*.*"
'Display *.* by default
FilterIndex = 2
'Set up the dialog box caption
Title = "Select a File to Import"
'Get the filename
FileName = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)
'Handle return info from dialog box
If FileName = False Then
MsgBox " No file was selected"
Else
MsgBox " You selected" & FileName
End If
Workbooks.Open FileName
' Change the application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
' Add a new workbook with one sheet.
Set DestWbk = Workbooks("ASL Summary.xls")
Cnum = 1
' Loop through all of the files in the myFiles array.
sourceRange = Worksheets("FORM").Range("D8:D9")
'Copy the file name in the first row.
With sourceRange
DestWbks.Cells(1, Cnum). _
Resize(, .Columns.Count).Value = MyFiles(FNum)
End With
' Set the destination range.
Set destrange = DestWbks.Cells(2, Cnum)
' Copy the values from the source range
' to the destination range.
With sourceRange
Set destrange = destrange. _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
Cnum = Cnum + SourceCcount
SourceWbk.Close savechanges:=False
DestWbks.Columns.AutoFit
ExitTheSub:
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub
i desperately need some help here. I need to create an Excel file that transfer data from a workbook which is the Form, to another workbook which is the Summary file.
Because the data is not in a continuous chain, the data need to copy and paste into the cells individually. This Summary file will be updated on an ongoing basis and will be used by several users. The Form and Summary will be in different Workbook when it is being used, but I can only attach one file for this forum so they are in the same workbook.
Below is the code that I have written but I think there are a lot of bugs as I can't run it.
There's alot of things that I'm not sure about the VBA code, like how to find the available row, how to set the destination file as the summary file and how should i write the VBA code. Should I use the "=" operator or should I use the copy method? There are still many questions on my mind but i'm quite lost here.:doh:
So any help is greatly appreciated!: pray2:
Sub MergeHorizontally()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim SourceCcount As Long, FNum As Long
Dim SourceWbk As Workbook, DestWbks As Workbook
Dim sourceRange As Range, destrange As Range
Dim Cnum As Long, CalcMode As Long
Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
'Set up list of file filters
Finfo = "Text files (*.txt),*.txt," & _
"All Files(*.*),*.*"
'Display *.* by default
FilterIndex = 2
'Set up the dialog box caption
Title = "Select a File to Import"
'Get the filename
FileName = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)
'Handle return info from dialog box
If FileName = False Then
MsgBox " No file was selected"
Else
MsgBox " You selected" & FileName
End If
Workbooks.Open FileName
' Change the application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
' Add a new workbook with one sheet.
Set DestWbk = Workbooks("ASL Summary.xls")
Cnum = 1
' Loop through all of the files in the myFiles array.
sourceRange = Worksheets("FORM").Range("D8:D9")
'Copy the file name in the first row.
With sourceRange
DestWbks.Cells(1, Cnum). _
Resize(, .Columns.Count).Value = MyFiles(FNum)
End With
' Set the destination range.
Set destrange = DestWbks.Cells(2, Cnum)
' Copy the values from the source range
' to the destination range.
With sourceRange
Set destrange = destrange. _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
Cnum = Cnum + SourceCcount
SourceWbk.Close savechanges:=False
DestWbks.Columns.AutoFit
ExitTheSub:
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub