Excel VBA to Work Across Workbooks

1
votes

I am very new to VBA coding and don't have very good understanding of what I am doing to be honest. But here I go.

I am looking to see if:

  • Can VBA codes have dyname values? So instead of the code saying execute on a set sheet (e.g "Sheet1") that value changes depending a value in a certain cell.
  • To trigger a VBA on another workbook. For example I want to run a VBA from Workbook A that triggers a VBA on Workbook B.

To fully explain I want to open Workbook A (and Workbook B if needed, it doesn't matter) and click a button that runs a VBA on Workbook B but on a certain Sheet depending on the value of a cell in Excel A (if the cell says "sheet3" the VBA runs on "sheet3" on Workbook B). I also want cells in Workbook A to reference cells in Workbook B but the the sheet name to by dynamic. For example I have pasted the basic cell reference bellow but instead of having Sheet1 I want it to change depending on the value in a cell. 

='[Workbook B.xlsx]Sheet1'!$A$4

I know this sounds very complicates and confusing, but if I could get any help that would be greatly appreciated.

Sub ReportStepOne() 
    Dim myRow As Long 

    myRow = 4 
    Rows(myRow).Value = Rows(myRow).Value 

    Dim rng As Range 
    Set rng = Range("A4:AC200") 
    rng.Cut rng.Offset(1, 0) 
    Range("A1:AC1").Copy Range("A4:AC4") 
End Sub

I want to:

  1. edit this code to make it fire on a certain sheet
  2. make it so the sheet name is referenced to whatever is in cell A o Sheet2 in Report.xlsm.
  3. Run a macro in Report.xlsm that runs the above script (which is called "StepOne" in a file called "Historical Data.xlsm"
2
vbaexcel
In which workbook does the above code reside, Report.xlsm or Historical Data.xlsm? If I understand correctly this code should execute on a sheet in Report.xlsm, which sheet is based on a cell on sheet2 in Report.xlsm – SilentRevolution
The code is on Historical data.xlsm and currently fires on the active sheet. I'm hoping to make it fire on a specific sheet, in which the macro references the "sheet name" from a cell in Reports.xlsm. Then have a macro/button in Reports.xlsm that makes the above macro run. It's a new reporting system that I am trying to develop (I'll have to run this macro approximately 40 times). Thanks for your help @SilentRevolution. Sorry about the confusion. – Jerremy Leedham
so the above code sits in historical data.xlsm needs to reference a worksheet to retrieve a worksheet name in reports.xlsm and then execute the code. the sheet named on sheet 2 of reports.xlsm does that referer to a sheet in historical data or to a sheet on reports.xlsm? Do you always have both workbooks open at the same time, if not which workbook is open? – SilentRevolution
The code would reference cell A4 on Sheet2 in reports.xlsm. Yeah the sheet name in reports.xlsm refers to a sheet name in historical data.xlsm. Cell A4 in reports.xlsm will automatically update with a new clients name, and each client has there own sheet in historical data.xlsm. I'll have both files open at the same time. – Jerremy Leedham

2 Answers

0
votes

The code below takes the value of cell A4 on sheet2 in Reports.xlsm and sets the ws variable to the sheet in Historical data.xlsm which is then used for the rest of the code. If possible I'd advise against having your subs spread out over multiple projects but that is just my opinion. I think it is easier to use proper referencing like below.

Since you want a button trigger on the Report.xlsm I'd suggest moving this code to that workbook. If properly referenced it you can open, edit, save and close any workbook from a single project which again, in my opinion is easier than calling subs in a different project.

Sub ReportStepOne()
    Dim wbHis As Workbook, wbRep As Workbook
    Dim strWsName As String
    Dim ws As Worksheet

    Set wbHis = Workbooks("Historical data.xlsm")
    Set wbRep = Workbooks("Reports.xlsm")

    strWsName = wbRep.Worksheets("Sheet2").Cells(4, 1)
    Set ws = wbHis.Worksheets(strWsName)

    With ws
        With .Rows(4)
            .Value = .Value
        End With
        With .Range("A4:AC200")
            .Cut .Offset(1, 0)
        End With
        .Range("A1:AC1").Copy .Range("A4:AC4")
    End With

End Sub
0
votes

To trigger a VBA on another workbook

Option Explicit
Sub RunVBA()

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Workbook


    Set xlApp = New Excel.Application
    Set xlWorkBook = xlApp.Workbooks.Open("C:\Users\Om3r\Desktop\Book1.xlsm")
    xlApp.Visible = True

    xlWorkBook.Application.Run "Module1.SubName" ' Modulename.Subname

End Sub

To reference worksheet use

Sub CopyRange()
    '// From sheet1 to sheet2
    Worksheets(2).Range("A1").Value = Worksheets(1).Range("A1").Value
End Sub