Hi,
I need help with a macro. I can't manage to get this to work
What I want to do is the following:
I got two files, and I need to transfer data from one file to another. One file always has the same name (ie. bb.xls) and location, another file has a variable name/location, ie. I need to use the file which is currently open. I do this with
var_filename = ActiveWorkbook.Name
BUT when I try to use this active workbook in the following code
ActiveCell.FormulaR1C1 = "=var_filename!w_nr"
it always gives an error ...
Can anybody help me out? And please be gentle with me as I am a newbie.
Thanks
I need help with a macro. I can't manage to get this to work
What I want to do is the following:
I got two files, and I need to transfer data from one file to another. One file always has the same name (ie. bb.xls) and location, another file has a variable name/location, ie. I need to use the file which is currently open. I do this with
var_filename = ActiveWorkbook.Name
BUT when I try to use this active workbook in the following code
ActiveCell.FormulaR1C1 = "=var_filename!w_nr"
it always gives an error ...
Can anybody help me out? And please be gentle with me as I am a newbie.
Thanks
Code:
Sub move_to_bb_xls()
'
'
' get current filename
var_filename = ActiveWorkbook.Name
' MsgBox (var_filename)
' open file
Workbooks.Open Filename:="C:w920bb.xls"
'search first new line
Range("A1").Select
Range("A500").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
' place data in BB.XLS
' ActiveCell.FormulaR1C1 = "=var_filename!w_nr" ' WHY DOESN'T THIS WORK?????
ActiveCell.FormulaR1C1 = "='cos design16.XLS'!w_nr"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='cos design16.XLS'!ordernr"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='cos design16.XLS'!date_bb"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='cos design16.XLS'!acpost"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='cos design16.XLS'!oa_name"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='cos design16.XLS'!end_bb"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='cos design16.XLS'!budget"
Application.Goto Reference:="R1C1:r1000c7"
Selection.Copy
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = True
Selection.Font.Bold = False
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.ColorIndex = xlAutomatic
End With
ActiveCell.Select
' Workbooks.Save Filename:="c:w920bb.xls"
' Workbooks.Close Filename:="c:w920bb.xls"
End