CODEWITHSUNDEEP
excelvba
question1234
question1234

Reputation: 1

How to get macros to work with changing sheet/workbook names

I'm writing a code to have information copied from "Workbook1" and pasted to "Master Worksheet". The macro works but instead of hardcoding the worksheet and workbook name I want to have it reference the cells in the "Macro" tab of "Master Worksheet.xlsx" that will specify the worksheet and workbook name. Similar to how I did it for Set survey = in line 2. This is because the worksheet and workbook name may change. I've been trying for quite a while but haven't had any success.

Dim survey As Workbook

Set survey = Workbooks.Open(Filename:=Sheets("Macro").Range("B5").Value & "\" & Sheets("Macro").Range("B6").Value)
  
Dim wsCopy As Worksheet

Dim wsDest As Worksheet

Dim lCopyLastRow As Long

Dim lDestLastRow As Long

  Set wsCopy = Workbooks("Workbook1.xlsx").Worksheets("Sheet1")

  Set wsDest = Workbooks("Master Worksheet.xlsm").Worksheets("Survey Answers")
    
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

  wsCopy.Range("J3:AQ" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)
    
  Workbooks("Workbook1.xlsx").Close SaveChanges:=True

Upvotes: 0

Views: 113

Answers (1)

Mech
Mech

Reputation: 4015

Assuming the Macro worksheet is located in the Master Worksheet workbook, this should work for you.

Sub ImaMacro()
    Dim wbDest As Workbook: Set wbDest = Workbooks("Master Worksheet.xlsm")
    Dim wbCopy As Workbook: Set wbCopy = Workbooks("Workbook1.xlsx")
    Dim wsDest As Worksheet: Set wsDest = wbDest.Worksheets("Survey Answers")
    Dim wsCopy As Worksheet: Set wsCopy = wbCopy.Worksheets("Sheet1")
    Dim survey As String
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long
    
    survey = wbDest.Sheets("Macro").Range("B5").Value & "\" & wbDest.Sheets("Macro").Range("B6").Value
    
    Workbooks.Open Filename:=survey
    
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

    wsCopy.Range("J3:AQ" & lCopyLastRow).Copy Destination:=wsDest.Range("A" & lDestLastRow)
    
    Workbooks("Workbook1.xlsx").Close SaveChanges:=True
End Sub

Upvotes: 1

Related Questions