Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data transfer, from template to main speadsheet
Hi
I have a daily template that is completed by some team members, this has to be in a format that is easy to read and use for people not used to spreadsheets. However, I need to transfer the data to the main spreadsheet, in a different format so that it can be used to generate reports. I want to use excel as those that will be using the reports don't need to be trained. The input sheet will have a unique date on the form, this is also already present in the main workbook, and will act as the key to link the data extracted to the correct location. The data is in the form of an easy questionnaire, but needs to be translated into a single row of information for each date. The format of the source and destination will not change, but the data will need to be placed against the date row of the destination sheet. So the question is, how do I automatically transfer the information from the first (remembering that there will be around 20 of these each month) to the second (only ever one)? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data transfer, from template to main speadsheet
You could use a macro like this - just change the specifics, of course. The
code goes into the summary workbook, which has a sheet name "Summary Sheet" with dates down column A. The data files have the key date in cell B1 of the default worksheet. And then the values in cellsC1, F16, H3, I9, and B12 are transfered to columns B, C, D, E, and F of the summary sheet. Sub PullData() Dim myR As Long Dim mySR As Range Dim myD As Range Workbooks.Open Application.GetOpenFilename(, , "Select the workbook") Set myD = Range("B1") Set mySR = ThisWorkbook.Worksheets("Summary Sheet").Range("A:A") myR = WorksheetFunction.Match(CDbl(myD.Value), mySR, False) With ThisWorkbook.Worksheets("Summary Sheet") ..Range("B" & myR).Formula = Range("C1").Value ..Range("C" & myR).Formula = Range("F16").Value ..Range("D" & myR).Formula = Range("H3").Value ..Range("E" & myR).Formula = Range("I9").Value ..Range("F" & myR).Formula = Range("B12").Value End With End Sub HTH, Bernie MS Excel MVP "danish404" wrote in message ... Hi I have a daily template that is completed by some team members, this has to be in a format that is easy to read and use for people not used to spreadsheets. However, I need to transfer the data to the main spreadsheet, in a different format so that it can be used to generate reports. I want to use excel as those that will be using the reports don't need to be trained. The input sheet will have a unique date on the form, this is also already present in the main workbook, and will act as the key to link the data extracted to the correct location. The data is in the form of an easy questionnaire, but needs to be translated into a single row of information for each date. The format of the source and destination will not change, but the data will need to be placed against the date row of the destination sheet. So the question is, how do I automatically transfer the information from the first (remembering that there will be around 20 of these each month) to the second (only ever one)? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data transfer, from template to main speadsheet
Berine,
sorry for not replying quickly. Tried this out and it works a treat, changed the variables, added a few more lines does the job fantastic! (did remove one of the stops at the beginning of this line "..Range("B" & myR).Formula = Range("C1").Value" but that might be due to my version of excel, but I'm not sure 2003) one additional question if I may, and if you have time, how do i make it open more that one input sheet at a time and work its magic? (they all have different names) but thats for all your help is solving this, great job! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data transfer, from template to main speadsheet
danish,
The double stops is an artifact introduced by some newsreaders... but you did the right thing to take one out. Anyway, to process mutliple files, you can do something like this, assuming that each of the data files is a single sheet, or if the active sheet is the desired sheet. Uncomment out the line 'myB.Worksheets("Sheet Name with the data").Select and change the sheet name if that is not the case: Sub PullData2() Dim myR As Long Dim mySR As Range Dim myD As Range Dim myArr As Variant Dim i As Integer Dim myB As Workbook myArr = Application.GetOpenFilename(, , "Select all the workbooks", , True) For i = LBound(myArr) To UBound(myArr) Set myB = Workbooks.Open(myArr(i)) 'myB.Worksheets("Sheet Name with the data").Select 'Code from here is the same as what you had previously Set myD = Range("B1") Set mySR = ThisWorkbook.Worksheets("Summary Sheet").Range("A:A") myR = WorksheetFunction.Match(CDbl(myD.Value), mySR, False) With ThisWorkbook.Worksheets("Summary Sheet") ..Range("B" & myR).Formula = Range("C1").Value ..Range("C" & myR).Formula = Range("F16").Value ..Range("D" & myR).Formula = Range("H3").Value ..Range("E" & myR).Formula = Range("I9").Value ..Range("F" & myR).Formula = Range("B12").Value End With 'End of code from before myB.Close False Next i End Sub HTH, Bernie MS Excel MVP "danish404" wrote in message ... Berine, sorry for not replying quickly. Tried this out and it works a treat, changed the variables, added a few more lines does the job fantastic! (did remove one of the stops at the beginning of this line "..Range("B" & myR).Formula = Range("C1").Value" but that might be due to my version of excel, but I'm not sure 2003) one additional question if I may, and if you have time, how do i make it open more that one input sheet at a time and work its magic? (they all have different names) but thats for all your help is solving this, great job! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Data from Excel Graph onto New Speadsheet | Charts and Charting in Excel | |||
How do I copy data from main frame computer and keep data in cell | Excel Worksheet Functions | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
Data transfer from a template to a workbook | Excel Worksheet Functions | |||
Access from add_in userform to main template userform.... | Excel Programming |