Want to format one workbook but i am wrinting the Macro in another workbook

Question

I have one workbook where I have some userform and code. Now based on this macro I am fetching the data into another workbook. Now From first excel I want to format the 2nd workbook after getting required data.

Below is my code:

 Set abc = objWorkbook1.Sheets(report_shtnm)
     abc.Activate
   With Sheet2.Range(Cells(4, 1), Cells(rw_reps_sht, cl_reps_sht))
    .Borders.Weight = xlThin
    .WrapText = True
   End With

The problem is its formatting the excel which has macro. Please help.

Are you facing an issue to pass the data? Can we split the code in WB1 and WB2 accordingly? — aksappy

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-02-19T07:51:57

The best way is to declare your objects correctly and then simply work with them... See this example

Option Explicit

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet

    '~~> This workbook which has the macro
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Sheets("Sheet")

    '~~> The other workbook
    Set wb2 = Workbooks.Open("C:\Sample.xlsx")
    Set ws2 = wb2.Sheets("Sheet1")

    '~~> Work with sheet1 in the workbook which has the macro
    With ws1
        '
        '~~> Your code here
        '
    End With

    '~~> Work with sheet1 in the second workbook
    With ws2
        '
        '~~> Your code here
        '
    End With
End Sub

If you notice that using this method doesn't even require you to use .Select or .Activate ;) You might also want to read this regarding .Select or .Activate

Want to format one workbook but i am wrinting the Macro in another workbook

1 Answers