Display same values in 3 differnt sheets.

[rapsingekar]

Hello Everyone,

I am new to programming, I am sorry if my question is too simple.

I have a workbook with 37 sheets,it is basically for tax calculation.
- I will get a Start and end date and I will break them in to Calendar year -Fiscal year. Which again depends on the Country selected for Home and Host from other sheet.With all the bordering in run time.
-So I need this display on 3 differnt sheets on one single click
-I have written[copied the same macro] twice. I know excel vba executes sequentially.
-While every thing runs and works beautifully it takes way lot of time...to get executed.
-Is there a way out so that I can get the thing executed quickly....as far as I know I have followed the Global variable declaration etc which can reduce quite a bit of time.
Thank you for the help
Regards
Ram

[ragulduy]

Can you post the code you have so far?
Using
Application.screenupdating = false
and
application.screenupdating = true
before and after the main body of code is usually the first step.

[rapsingekar]

Here is part of my code ....I have done the application.screenupdating... I have the same piece of code with another country refering in another sheet which will display values on two other sheets
Thanks for the quick response.

Sub noDays()
    
'Unportecting the sheet to make changes to the cells
    ActiveWorkbook.Worksheets("Host Txbl Wages").Unprotect
    ActiveWorkbook.Worksheets("Home Txbl Wages").Unprotect
    ActiveWorkbook.Worksheets("Home Hypo Wages").Unprotect
    
'Screen updating would not take place rather we would see a rolling circle
    Application.ScreenUpdating = False
    
'i is set as the column start
    i = 4
    
'was used for the off set cell calculation in tax calculation
    j = 0
    
'calls the function which clears the borders
    Call clearBorder
    
'calls the function which clears the data filled in the tables due to excel formulae calculation with out deleting the actual formulae
    Call DaysClear
    
'gets the Assignment start date
    stDate = ActiveWorkbook.Worksheets("Input Section").Range("E15").Value
    
'gets the Assignment end date
    enDate = ActiveWorkbook.Worksheets("Input Section").Range("E16").Value
    
'checks if the endate was given less than the start date
    If enDate < stDate Then
        MsgBox "End date cannot be less than Start date!!"
        Exit Sub
    End If
    
'total no.of year of assignment
    yrdiff = Year(enDate) - Year(stDate)
    
'if the assignment finishes with in one year, we would just display the start date and endate
    If yrdiff = 0 Then
        
        ActiveWorkbook.Worksheets("Host Txbl Wages").Cells(122, i).Value = stDate
        ActiveWorkbook.Worksheets("Home Txbl Wages").Cells(122, i).Value = stDate
        ActiveWorkbook.Worksheets("Home Hypo Wages").Cells(122, i).Value = stDate
        
        ActiveWorkbook.Worksheets("Host Txbl Wages").Cells(123, i).Value = enDate
        ActiveWorkbook.Worksheets("Home Txbl Wages").Cells(123, i).Value = enDate
        ActiveWorkbook.Worksheets("Home Hypo Wages").Cells(123, i).Value = enDate
        
'display the calendar year
        ActiveWorkbook.Worksheets("Host Txbl Wages").Cells(126, i).Value = Year(enDate)
        ActiveWorkbook.Worksheets("Home Txbl Wages").Cells(126, i).Value = Year(enDate)
        ActiveWorkbook.Worksheets("Home Hypo Wages").Cells(126, i).Value = Year(enDate)
        
'Bordering for the data which was displayed
        Sheets("Host Txbl Wages").Activate
        Range("D122:D155").Select
        Call bordering
        
'if the assignment lasts for more than a year
    Else
        
' set the first date of the table as assignment start date
        hostStartYear = stDate
        
'set the end of the calendar year
        hostEndYear = DateSerial(Year(hostStartYear), 12, 31)
        
'comes out of the loop once the  hostEndYear becomes greater than the assignment end date
        While enDate > hostEndYear
            
'enDate would always be greater than hostEndYear if not the first condtion would make it work
            If enDate > hostEndYear Then
                
'set the first cell
                ActiveWorkbook.Worksheets("Host Txbl Wages").Cells(122, i).Value = hostStartYear
                ActiveWorkbook.Worksheets("Home Txbl Wages").Cells(122, i).Value = hostStartYear
                ActiveWorkbook.Worksheets("Home Hypo Wages").Cells(122, i).Value = hostStartYear
                
'the next cell as the host end year
                ActiveWorkbook.Worksheets("Host Txbl Wages").Cells(123, i).Value = DateSerial(Year(hostStartYear), 12, 31)
                ActiveWorkbook.Worksheets("Home Txbl Wages").Cells(123, i).Value = DateSerial(Year(hostStartYear), 12, 31)
                ActiveWorkbook.Worksheets("Home Hypo Wages").Cells(123, i).Value = DateSerial(Year(hostStartYear), 12, 31)
                
'display the calendar year
                ActiveWorkbook.Worksheets("Host Txbl Wages").Cells(126, i).Value = Year(hostEndYear)
                ActiveWorkbook.Worksheets("Home Txbl Wages").Cells(126, i).Value = Year(hostEndYear)
                ActiveWorkbook.Worksheets("Home Hypo Wages").Cells(126, i).Value = Year(hostEndYear)
                
'set the hostStart a year more for the next iteration
                hostStartYear = DateSerial(Year(hostEndYear) + 1, 1, 1)
                
'sets the end year of the hostStartYear
                hostEndYear = DateSerial(Year(hostStartYear), 12, 31)
                
            End If
            
'increment the column
            i = i + 1
            
'increments the column value in wages/allowances
            j = j + 1
'ends while when the hostEndYear becomes more than the assignment endDate and then display the start of the year and the end Date
            
        Wend
        
'the first cell in the last column
        ActiveWorkbook.Worksheets("Host Txbl Wages").Cells(122, i).Value = hostStartYear
        ActiveWorkbook.Worksheets("Home Txbl Wages").Cells(122, i).Value = hostStartYear
        ActiveWorkbook.Worksheets("Home Hypo Wages").Cells(122, i).Value = hostStartYear
        
'the last cell in the range of the last column
        ActiveWorkbook.Worksheets("Host Txbl Wages").Cells(123, i).Value = enDate
        ActiveWorkbook.Worksheets("Home Txbl Wages").Cells(123, i).Value = enDate
        ActiveWorkbook.Worksheets("Home Hypo Wages").Cells(123, i).Value = enDate
        
'display the calendar year
        ActiveWorkbook.Worksheets("Host Txbl Wages").Cells(126, i).Value = Year(enDate)
        ActiveWorkbook.Worksheets("Home Txbl Wages").Cells(126, i).Value = Year(enDate)
        ActiveWorkbook.Worksheets("Home Hypo Wages").Cells(126, i).Value = Year(enDate)
        
'Borders the table
        Call tableDays
        
'ends the initial condtion of the assignment duration of one or more years
    End If
    
'Font size and alignment
'Activates the the sheet where we need the font alignment
Call align
'calls the second table
    Call daysInFiscal
    
    
'proceeds to convert in to Fiscal year
    Call hostYearConversion
    
'calls the last Fiscal to Calendar Conversion
    Call FiscalToCalendar
    
 ActiveWorkbook.Worksheets("Host Txbl Wages").Range("$A$4:$A$114").AutoFilter Field:=1, Criteria1:="1"
'End of the funtion
End Sub

Moderators note: code tags added for you - this time

[Naveed Raza]

Please use code tag while posting code on thread edit your thread and follow the below step
type like this below
[code]
here your code
[\code]

[FDibbins]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[rapsingekar]

I am really sorry for this ..I will come back with the question and the code correctly formatted as per the rules. I see how annoying it looks. Thanks a lot for your inputs .

[ragulduy]

Other than your while loop there doesn't seem anything in that block that would slow it down considerably. You can try setting the stuff in the loop to an array and then assigning that to the worksheet range in one go. It's hard to say how much use that would be without all the other moacrosthat get called up.

Could you edit your post to include code tags and maybe post the workbook in question if possible (removing all sensitive data).

[rapsingekar]

Hi Yudlugar,
Thanks a lot I will update the Workbook. Thank you and sorry if you had to put in extra effort to go through the code.