CODEWITHSUNDEEP
vbaexcel
DA69
DA69

Reputation: 91

Dynamic Workbook and Worksheet name in Macro or VBA

I have this code that works: (am currently using Excel 2003)

Sub test_copy1()    

Dim wb1 As Workbook     
Dim wb2 As Workbook     
Dim ws1 As Worksheet     
Dim ws2 As Worksheet      

Set wb1 = Workbooks("Workbook2.xls")     
Set wb2 = ThisWorkbook     
Set ws1 = wb1.Sheets("Run 1")     
Set ws2 = wb2.Sheets("Sheet3") 

ws2.Range("A1:h30") = ws1.Range("A177:H206").Value  

End Sub

In the current Workbook "Sheet 3" Cell J1 = Workbook2.xls Cell J2 = Run 1

How can I feed the information from the current Workbook "Sheet 3" into the macro from the cells so that it may be changed as needed and still work?

What I mean is to have 2 cells that can change in a sheet, either by drop down or manually filled in on the current workbook sheet "sheet 3". Cell: J1 would equal a file name (Workbook2.xls) and J2 would equal a sheet name (Run 1).

The user would be able to change the file name and sheet name when they needed and then hit a button to process the copy and paste.

I would like the code to take the information from the cells and put them into the code instead of "hard" coding the file name of the workbook and sheet.

I hope that makes more sense

Thank you for any help.

~DA

-----UPDATE----

I have tried this code but it keeps crashing on the first Set Wb1=

    Sub test_copy1()

        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet

        Set wb1 = Workbooks(ws2.Range("J1"))
        Set wb2 = ThisWorkbook
        Set ws1 = wb1.Sheets(ws2.Range("J2"))
        Set ws2 = wb2.Sheets("Sheet3")

        ws2.Range("A1:h30") = ws1.Range("A177:H206").Value

    End Sub

------Update -------

I got it to work using you all examples and adding .Value after the range :)

Sub test_copy1()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet

    Set wb2 = ThisWorkbook
    Set ws2 = wb2.Sheets("Sheet3")

    Set wb1 = Workbooks(ws2.Range("J1").Value)
    Set ws1 = wb1.Sheets(ws2.Range("J2").Value)

    ws2.Range("A1:h30") = ws1.Range("A177:H206").Value

End Sub

Thank you, thank you for all your help!!

Upvotes: 2

Views: 33844

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

Try this one:

Sub test_copy1()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet

    Set wb2 = ThisWorkbook
    Set ws2 = wb2.Sheets("Sheet3")

    Set wb1 = Workbooks(ws2.Range("J1").Value)
    Set ws1 = wb1.Sheets(ws2.Range("J2").Value)

    ws2.Range("A1:H30").Value = ws1.Range("A177:H206").Value

End Sub

Upvotes: 4

Related Questions