Macro pasting

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

therealtimmy

Thread Starter
Joined
Feb 2, 2013
Messages
2
Hi All,

I've created a formula to give me a file path that i need to draw data from, i can't work out how to now get the file path to act as a formula in itself, any thoughts?

Formula:
="='T:\Supply Chain\Material Planning\MRP back ups\"&B5&"\20"&(RIGHT(TEXT(SUM(VLOOKUP(SUM(VLOOKUP(TODAY(),'Week from Board'!E:F,2,FALSE)+1),'Week from Board'!A:B,2,FALSE)-3),"DDMMYY"),2))&"\"&MID(TEXT(SUM(VLOOKUP(SUM(VLOOKUP(TODAY(),'Week from Board'!E:F,2,FALSE)+1),'Week from Board'!A:B,2,FALSE)-3),"DDMMYY"),4,1)&" "&TEXT(SUM(VLOOKUP(SUM(VLOOKUP(TODAY(),'Week from Board'!E:F,2,FALSE)+1),'Week from Board'!A:B,2,FALSE)-4),"MMM")&"\["&TEXT(SUM(VLOOKUP(SUM(VLOOKUP(TODAY(),'Week from Board'!E:F,2,FALSE)+1),'Week from Board'!A:B,2,FALSE)-3),"DDMMYY")&C5&".xlsm]MRP Viewer'!A1"

File Path:
='T:\Supply Chain\Material Planning\MRP back ups\Fruit\2013\2 Feb\[060213F.xlsm]MRP Viewer'!A1

When i paste the values that the formula creates, it just stays as text, i need it to actually be the formula above...

TjP
 
Joined
Sep 4, 2003
Messages
4,912
Try using the .formula property. Just put your actual formula text after the equals sign.

Range("A1").Formula =

OR

Activecell.Formula =
 

therealtimmy

Thread Starter
Joined
Feb 2, 2013
Messages
2
Try using the .formula property. Just put your actual formula text after the equals sign.

Range("A1").Formula =

OR

Activecell.Formula =
Thank you for coming back to me, is this something i can build into the formula or the Macro? I'm not too bad in excel, but Macro wise i'm a novis. This is the string from the Macro i recorded... Where should i add your logic?

Sub Import_MRP_Viewers()
'
' Import_MRP_Viewers Macro
'

'
Range("A1").Select
Selection.Copy
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='T:\Supply Chain\Material Planning\MRP back ups\Veg\2013\1 Jan\[270113V.xlsm]MRP Viewer'!R[-6]C"
Range("A7").Select
Selection.Copy
Range("A7:AL7").Select
ActiveSheet.Paste
Range("A7").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A7:AL1010"), Type:=xlFillDefault
Range("A7:AL1010").Select
Range("AJ1010").Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=-21
Range("A2").Select
Selection.Copy
Range("A13").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=15
Range("A1011").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='T:\Supply Chain\Material Planning\MRP back ups\Protein\2013\1 Jan\[270113P.xlsm]MRP Viewer'!R[-1010]C"
Range("A1011").Select
Selection.Copy
Range("A1010").Select
Selection.End(xlToRight).Select
Range("AL1011").Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveSheet.Paste
Range("AK1011").Select
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A1011:AL2020"), Type:=xlFillDefault
Range("A1011:AL2020").Select
Range("W2020").Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("A3").Select
Selection.Copy
Range("A13").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=12
Range("A2021").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='T:\Supply Chain\Material Planning\MRP back ups\Whole Head\2013\1 Jan\[270113W.xlsm]MRP Viewer'!R[-2020]C"
Range("A2021").Select
Selection.Copy
Range("A2020").Select
Selection.End(xlToRight).Select
Range("AL2021").Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveSheet.Paste
Range("AK2021").Select
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A2021:AL3030"), Type:=xlFillDefault
Range("A2021:AL3030").Select
Range("AG3030").Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=-21
Range("A4").Select
Selection.Copy
Range("A17").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=15
Range("A3031").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='T:\Supply Chain\Material Planning\MRP back ups\Baby leaf\2013\1 Jan\[270113B.xlsm]MRP Viewer'!R[-3030]C"
Range("A3031").Select
Selection.Copy
Range("A3030").Select
Selection.End(xlToRight).Select
Range("AL3031").Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveSheet.Paste
Range("AK3031").Select
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A3031:AL4040"), Type:=xlFillDefault
Range("A3031:AL4040").Select
Range("AE4040").Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=12
Range("A4041").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='T:\Supply Chain\Material Planning\MRP back ups\Fruit\2013\1 Jan\[270113F.xlsm]MRP Viewer'!R[-4040]C"
Range("A4041").Select
Selection.Copy
Range("A4040").Select
Selection.End(xlToRight).Select
Range("AL4041").Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A4041:AL5050"), Type:=xlFillDefault
Range("A4041:AL5050").Select
End Sub
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Staff online

Members online

Top