• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA sheet related doubt.

J

Jagdev Singh

Active Member
Hi Experts

I have a doubt. Is it possible to use the code to inform the macro file which sheet (1, 2, and 3)/dynamic ally to be pulled as a input file from a common folder. Say in code instead of

Set ws = wb.Worksheets(1)

Which says to consider sheet1 from the workbook and I want to inform the macro to pull the sheet depends upon the cell value in the macro file.

Somewhat like this as an example. It is practically not going to work just trying to clarify my concerned

Set ws = wb.Worksheets(ActiveWorkbook.Sheets(2).Range("E8").Value)

I will add a number whatever 1, 2, 3 in the cell E8 and the code will consider that sheet as an input.

Regards,

JD
 
JD

Let me explain how sheet can be referred.

A. Sheets(sheet status form left )
B. Sheetn (where n = Sheet code name appears in VBA)
C. Sheets("sheet name")
 
Hi Deepak

Thanks for explanation. I am aware of few, but I was thinking is it possible to call the sheet from the cell value. Just a random doubt.

Regards,
JD
 
You can call like as

Code: 
Set ws = wb.Worksheets(Sheets(ActiveWorkbook.Sheets(2).Range("E8").Value))

or
if the E8 is numeric or want to ref sheet as numeric from left.
Set ws = wb.Worksheets(Sheets(ActiveWorkbook.Sheets(2).Range("E8").Value))
 
Hi Deepak

Thanks for the code, but I am not able to use it directly from my macro file, because my code first open a file from a folder and till the time it reach this line of code it consider the newly opened file and an active file and not able to get the number to proceed further. I really thank you for the concept I just manipulate it slightly and it fits into my need. What I did to get the desired output.

I first create the variable

Dim Sheetno As Long

I assigned the macro sheet cell value to this variable

Sheetno = ActiveWorkbook.Sheets(2).Range("E8").Value

In the code instead of calling sheet I called this variable

Set ws = wb.Worksheets(Sheetno)

I am more than happy to get the desire output with it. Thanks for sharing your concept with me. Really appreciate it.

Regards.

JD
 
Check something like as ..

Code: 
Dim Sheetno As Long, ws As Worksheet, wb As Workbook

Sheetno = ThisWorkbook.Sheets(2).Range("E8").Value

Set wb = Workbooks.Open("G:\entry\abcd.xlsx")

Set ws = wb.Worksheets(Sheetno)
 
Hi Deepak Sirji

I already solved the issue with your earlier concept. I even tried to explain you the procedure I followed to make it workable.

Regards,

JD
 
You must log in or register to reply here.
Back
Top