Solved: Copy data from workbooks to a Summary workbook

[Spider_Lily]

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

[MaximS]

Can you please attach form file ?

[Simon Lloyd]

The code in your workbook doesn't compile because you have an "End With" missing from the "With Application", you haven't said how many workbooks to summarise or from which file or path!

Check out our Kbase, you will find almost anything you need there!

[Spider_Lily]

Can you please attach form file ?


Hi MaximS,

I have attached the file and it is saved under the tab, "Form".

Thanks for your help!:bow:

[MaximS]

Please see attachement for details. I've added "Update Summary" button in form. Code will check first whether all form fields have been filled if not will stop with error msg. If yes will look for the last used row and paste the values into correct places.

There is one thing you have to do: fill source values for some of the validation lists i.e. BC130:131 otherwise it will not run.

I have tested that with fields filled in and it worked.

[Spider_Lily]

Hi Maxim

Thanks alot for your help, but just out of curiosity and my own knowledge, what does the following do?


Range("D6:D24").ClearContents
Range("F26").ClearContents
Range("H11:H18").ClearContents
Sheets("Summary").Select

[Simon Lloyd]

Below is the code that I have written but I think there are a lot of bugs as I can't run it.
judging by your comment in your first post and code and now the above statement i suspect you didn't write the code, Spider Lily, if you have posted these questions elsewhere you need to provide a link to those forums, for an explanation why please click the link in my signature.

[Spider_Lily]

Hi Simon,

Thanks for the info. I didn't know that in the first place. Sorry about tha:doh:

Actually the code that I have posted is written by me. But I cut and paste parts from different places to make up the code. Maybe that's why it may seem that other ppl have written it. I need help because I have been at it for a week and trying to understand what the codes mean to me..And if anyone could point out where i was wrong, I will be very grateful..

[Spider_Lily]

Just to share with all who may have the same problem.

Problem:

You have a Form in a Workbook in Excel Format which u have to transfer to a Summary Workbook below.

The cells in the form are all over the place, but you want to copy and paste the data into a table accordingly.

Note:
This code should be saved in the Summary Workbook.
The wordings in red are my explanation, pls delete them when you run the macro
The code is what I have written based on my understanding, If anyone has a code that will work better, I will appreciate it

FormI. Requestor InformationRequestor NameCell D6Business GroupCell H6Requestor EmailCell D7
The form above is in a workbook named Form.xls

Requestor NameRequestor emailBusiness GroupCell A2Cell B2Cell C2Cell A3Cell B3Cell C3

This table is in another Workbook named Summary.xls


Solution:

Sub UpdateSummary()
Dim DstWks As Worksheet
Dim LastRow As Long
Dim R As Long
Dim SrcWkb As Workbook
Dim wkbname As Variant
Dim xlsFiles As Variant

' Change the application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With


'Get the workbooks to open
xlsFiles = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
Application.AskToUpdateLinks = False
If VarType(xlsFiles) = vbBoolean Then Exit Sub




'Starting column for the destination workbook
C = 1

'Set references to destination workbook worksheet objects
Set DstWks = ThisWorkbook.Worksheets("Summary")

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For Each wkbname In xlsFiles
Set SrcWkb = Workbooks.Open(FileName:=wkbname, ReadOnly:=True)



With SrcWkb.Worksheets("FORM")
DstWks.Cells(LastRow + 1, C).Value = .Range("D6").Value
C = C + 1
DstWks.Cells(LastRow + 1, C).Value = .Range("D7").Value
C = C + 1
DstWks.Cells(LastRow + 1, C).Value = .Range("H6").Value

(If you have other cells you need to copy and paste, just use the same format as above. You only need to amend the portion: ."D6" above to the cells you need to copy. eg, You need to copy cell H13 and C14 from the Form to the Summary. The format is:

DstWks.Cells(LastRow + 1, C).Value = .Range("H13").Value
C = C + 1
DstWks.Cells(LastRow + 1, C).Value = .Range("C14").Value )

End With


SrcWkb.Close savechanges:=False

Next wkbname

ExitTheSub:
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With

End Sub

[Spider_Lily]

Didn't know that we can paste pictures here.

Anyway I attached the Dummy file.

They are supposed to be in separate Workbook. But we can only post one attachment here. Hence they are in the same workbook.

[MaximS]

Thanks alot for your help, but just out of curiosity and my own knowledge, what does the following do?




VBA:
Range("D6:D24").ClearContents Range("F26").ClearContents Range("H11:H18").ClearContents Sheets("Summary").Select



This code is clearing the form after updating summary part.