Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a source Excel sheet called Shipper VP. There are names of three persons in the cells B4,C4,D4 of the Shipper VP and corresponding data in the columns. I am extracting their data for each person and creating separate excel tabs for each person and renaming it to their respective name. Plus there are bunch of other calculations. This is the code: (everything works)

Sub Test()
    Dim wsSource As Worksheet, ws As Worksheet
    Dim rg As Range, c As Range
    Dim n As Integer 

    Set wsSource = Sheets("Shipper VP")
    Set rg = wsSource.Range("B4:D4")
    n = 1
    For Each c In rg
         'add sheet and rename
        Set ws = Sheets.Add
        ws.Name = c.Text

         'other calculations
         '...

        n = n + 1
    Next c
End Sub

However, at present I know that there are three persons in the Source sheet(ws.Source), I want to loop the same thing for “n” number of persons. For each person, it starts from B4, and continues to C4, D4, E4 etc. till the number of persons in the Source Sheet. The last column is for Grand Total but I extract data only till the previous column and not for the Grand Total column.

Now in the above code Instead of ("B4:D4") in the rg range, I want D4 cell to be a variable so that it can be anything like Z4 or AK4 etc. It there are three persons, the cell address it takes from the source sheet (wsSource) is from B4:D4, if there are 10 persons, it will be from B4:K4. If there are 100 persons again it will change to B4:x4, where x will be the (100+1)th column of the source excel sheet(wsSource).

I am not able to pass this variable to the following code: Set rg = wsSource.Range("B4:D4")

So, what it should do if there are "n" persons (which I may not know the number of persons) in the source sheet, there should be a variable which checks until the cell value starting from B4 is null, then in the next code it should automatically set the range for rg = wsSource.Range(“B4:x4”), where x is the (100+1)th column.

But during searching when there are three persons, it will get the null value in the F4 column and not in E4 column of the source sheet(Shipper VP), because the name of the persons are from B4 to D4, then there is an extra Grand Total column in E4. But in the range for rg, I want only from B4 to D4 and not till the E4 column. The same is to be done for "n" persons in the Source sheet.

If B4 is also a variable, then it is much better like ‘y’ or something. Then I can set ‘y’ as B4 as the starting cell number. And after that it will loop from B4 to the second last column value of Row 4 in the source sheet(ws.Source), since last column value of Row 4 will be Grand Total.

I will be really grateful if somebody can help me out in this.

Thanks. Regards, Dip

share|improve this question
add comment

1 Answer

up vote 1 down vote

You could modify your code to use a range based on cell numbers rather than their letter reference and grab the second last column in use with the following code.

Sub Test()
    Dim first As Long
    Dim last As Long
    Dim row As Long
    Dim wsSource As Worksheet, ws As Worksheet
    Dim rg As Range, c As Range
    Dim n As Integer 

    Set wsSource = Sheets("Shipper VP")
    first = 2
    row = 4

    With wsSource
        ' This returns the last column with data.  There could be empty cells along the way.
        'You could look into converting the column reference into the letter so that you could use a string in the range such as "B4:E4"
        last = .Cells(row, .Columns.Count).End(-4159).Column - 1 'xlToLeft
    End With

    Set rg = wsSource.Range(Cells(row, first), Cells(row, last))

    n = 1
    For Each c In rg
         'add sheet and rename
        Set ws = Sheets.Add
        ws.Name = c.Text

         'other calculations
         '...

        n = n + 1
    Next c
End Sub
share|improve this answer
 
Thanks a lot Zaider, for the help. The code is working. Bye –  user2453309 Jun 10 at 20:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.