Open file and import values current worksheet

[TDPorter]

I'm a novice at Excel trying to create some forms for work. My goal is to have a working workbook that holds all my values from multiple jobs that gets updated daily with additional worksheets that gets sent to me. I'd like to be able to hit a macro in my workbook that allows me to select a file that has been emailed to me and import their values into mine. For example:

While using my active workbook I want to select a file. Then take cells A1:A15 from selected file and paste to cells F1:F15 in my active workbook.

It may be a tall order but maybe someone out there has the time, much appreciated!

 

[gaj104]

Welcome to the board.

The simple code below should do what you are looking for. Change sheet names where required.

Sub CopyRange()

Dim wbS As Workbook
Dim ws As Worksheet, wsA As Worksheet
Dim myFile As String

'Make reference to the sheet to paste data into
Set wsA = ActiveSheet 'Alternatively set wsA = ThisWorkBook.Sheets("yoursheetname here")

'select file and store as variable
myFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

'Open seleted file using stored variable (open read only and dont update links for efficiency)
Set wbS = Workbooks.Open(myFile, False, True)

'Make reference to sheet that has data on
Set ws = wbS.Sheets("Sheet1") 'Change to name of sheet in imported file

'Copy range A1:A15 in sheet in workbook
ws.Range("A1:A15").Copy wsA.Range("F1:F15")

'Close selected workbook
wbS.Close False

End Sub

Hope it helps

 

[TDPorter]

So far so good! So the problem I'm having now is that when it copies the cells from the temp file it actually copying the formula. How can I have it paste special "values only"?

Dim wbS As Workbook
Dim ws As Worksheet, wsA As Worksheet
Dim myFile As String
'Make reference to the sheet to paste data into
Set wsA = ThisWorkbook.Sheets("overview")
'select file and store as variable
myFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
'Open seleted file using stored variable (open read only and dont update links for efficiency)
Set wbS = Workbooks.Open(myFile, False, True)
'Make reference to sheet that has data on
Set ws = wbS.Sheets("DAILY TALLY") 'Change to name of sheet in imported file
'Copy range in sheet in workbook
ws.Range("D31").Copy wsA.Range("O40")
ws.Range("O41").Copy wsA.Range("E31")
ws.Range("O42").Copy wsA.Range("F31")
ws.Range("O43").Copy wsA.Range("G31")
ws.Range("O44").Copy wsA.Range("H31")
ws.Range("O45").Copy wsA.Range("I31")
ws.Range("O46").Copy wsA.Range("J31")
ws.Range("O47").Copy wsA.Range("K31")
ws.Range("O48").Copy wsA.Range("L31")
ws.Range("O49").Copy wsA.Range("M31")
ws.Range("O50").Copy wsA.Range("N31")
ws.Range("O51").Copy wsA.Range("O31")
ws.Range("O52").Copy wsA.Range("P31")
ws.Range("O53").Copy wsA.Range("Q31")
ws.Range("O54").Copy wsA.Range("R31")
ws.Range("O55").Copy wsA.Range("S31")
'Close selected workbook
wbS.Close False