Macro to Open File with Variable Name and Copy Worksheet

[brezinst]

I need to write a macro that opens up a file with the name "<mm-dd-yy> Positions Insight" from a directory C:\Positions\ and copy a worksheet "Sheet1" and Paste Special Values into my original workbook called "MarketValues_<yyyymmdd>" on a sheet called "raw". Could someone please assist me in doing this? I can't seem to find an example anywhere.

Thanks so much in advance.

Todd</yyyymmdd></mm-dd-yy>

 

[brezinst]

Sorry, the file I'm opening is mm-dd-yy Positions Insight and the file I'm adding it too is MarketValues_yyyymmdd

The dates will always both be today.

Thanks

 

[VoG]

Hello and welcome to MrExcel.

Try this - the code goes in the MarketValues... workbook

Sub Cpy()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Positions\" & Format(Date, "mm-dd-yy") & "Positions Insight.xls")
Sheets("Sheet1").UsedRange.Copy
ThisWorkbook.Sheets("raw").Range("A1").PasteSpecial Paste:=xlPasteValues
wb.Close savechanges:=False
End Sub

 

[brezinst]

Works perfectly, thanks!

 

[Misca]

Try this:

Sub CopyAndPasteValues()

Dim WB1 As Workbook
Dim WB2 As Workbook

Set WB1 = ThisWorkbook
Sheets("Raw").Activate
'Finds the last row on Column A and selects the first empty cell below it
Range("A65536").End(xlUp).Offset(1, 0).Select

'Opens the workbook "Positions Insight.xls":
Workbooks.Open "C:\Positions\Positions Insight.xls"

Set WB2 = ActiveWorkbook
'Copies the A1 CurrentRegion:
Sheets("Sheet1").Range("A1").CurrentRegion.Copy

'Goes back to orginal workbook and pastes the values
WB1.Activate
ActiveCell.PasteSpecial xlValues

'Closes the workbook "Positions Insight.xls" without saving:
WB2.Close False
End Sub