Insert value from cell into macro

[FearofExcel]

Hi All,

I'm currently creating a report where I have a macro that pulls info from a workbook and pastes it into another when prompted by the user. This code looks like this:

Sub Button()



Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("C:\Users\myusername\documents\targetfilename.xls")
Set y = Workbooks.Open("C:\Users\myusername\documents\destinationfilename.xls")

x.Worksheets("Sheet1").Range("A13:I28").Copy

y.Sheets("Sheet1").Range("A13:I28").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

x.Close


End Sub

It works fine for me, the problem arises when another user tries to run it on their system as obviously the username changes rendering the pathways above invalid.

A solution I have thought of involves having the user enter their username as a value in a cell and having this value inserted into pathway in the VBA code.

For example if the user entered their username in cell A1, the pathway in the macro would update to "C:\Users\CellValue\documents\destinationfilename.xls".

Is there any way I can do this?

This is the first taste of programming I've had and I have to say the buzz you get when something works is awesome, but this problem is causing me a headache ha ha.

Thanks

 

[JackDanIce]

Welcome to the board. You can break up and down strings in VBA, so you could use something like:

Const sRoot as String = "C:\Users\"
Dim sUser as String
Dim x as Workbook

sUser = Range("A1").Value
Set x = Workbooks.Open(sRoot & sUser & "documents\destinationfilename.xls")