Defining last nine rows of data as range to copy values and transpose paste into another worksheet

0 votes

What VBA code should be used to provide a range that always includes the latest nine rows of data?

I have a data table with 9 subjects that is manually updated each month. I want to duplicate some cell values from the most recent entries and use them to fill out a scorecard on another worksheet.

Determining a dynamic range from the ninth row up is something I can't seem to figure out how to do.

I can define the last row using

Last = Cells(Rows.Count, “B”).End(xlUp).Row

But how do I define the last 9 rows?

Last = Cells(Rows.Count, “B”).End(xlUp).Row

‘First???

For i=Last To ‘first Step -1

‘If “name” then copy cell values to the defined cell in the scorecard

Jan 31, 2023 in Others by Kithuzzz
• 38,010 points
385 views

1 answer to this question.

0 votes

Transpose Last Rows

Sub PrintLastRowsAddress()

    Const LAST_ROWS_COUNT As Long = 9
 
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim lrg As Range

    With ws.UsedRange
        Set lrg = .Resize(LAST_ROWS_COUNT).Offset(.Rows.Count - LAST_ROWS_COUNT)
    End With
    
    Debug.Print lrg.Address(0, 0)
    
End Sub

enter image description here

Sub TransposeLastRows()

    Const SRC_NAME As String = "Sheet1"
    Const SRC_LAST_ROWS As Long = 9
    Const DST_NAME As String = "Sheet2"
    Const DST_FIRST_CELL As String = "A1"
 
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets(SRC_NAME)
    Dim srg As Range
    With sws.UsedRange
        Set srg = .Resize(SRC_LAST_ROWS).Offset(.Rows.Count - SRC_LAST_ROWS)
    End With
    
    Dim dws As Worksheet: Set dws = wb.Sheets(DST_NAME)
    Dim dfCell As Range: Set dfCell = dws.Range(DST_FIRST_CELL)
    Dim drg As Range: Set drg = dfCell.Resize(srg.Columns.Count, SRC_LAST_ROWS)
    
    drg.Value = Application.Transpose(srg.Value)
    
    MsgBox "Last " & SRC_LAST_ROWS & " rows transposed.", vbInformation
    
End Sub
answered Jan 31, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,700 points
507 views
0 votes
1 answer
0 votes
1 answer

Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,700 points
523 views
0 votes
0 answers

how to get another copy of my cert

Oct 1, 2019 in Others by Loletha
• 120 points
501 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
797 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,619 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,453 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,700 points
1,100 views
0 votes
1 answer

How to copy entire data using row by row and paste to another sheet

Try this: Sub InsertData() Dim ...READ MORE

answered Feb 5, 2023 in Others by narikkadan
• 63,700 points
1,304 views
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,700 points
1,773 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP