Transferring range of data to another worksheet

Question

In sheet1 I have data.
I want to export data from B4 to AN4 (name of company) and from B20 to AN20 (average score).

Selecting data from only B4 and B20 gives the result I want.

I want to set a range from B4 to AN4 for company and B20 and AN20 for average.

Sub results()      
    
    Dim company As String, average As Integer
    Worksheets("sheet1").Select
    company = Range("B4:AN4")
    average = Range("B20:AN4")
    Worksheets("sheet2").Select
    Worksheets("sheet2").Range("A4").Select
    If Worksheets("sheet2").Range("A4").Offset(1, 0) <> “” Then
        Worksheets("sheet2").Range("A4").End(xlDown).Select
    End If
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = company
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = average
    Worksheets("sheet1").Select
    Worksheets("sheet1").Range("C4").Select
    
End Sub

Worksheet example:

Read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Feb 24 '21 at 22:12

JohnnieL · Accepted Answer · 2021-02-24T22:33:43.510

a few things

When you scale from a single cell to multiple cells, you have not defined company and average as range but you are trying to set them to a range (you've declared as string and double respectively) so this line and the next will throw an error company = Range("B4:AN4"). If you have option explicit you will not permit these errors.
your average range is not correct. Should be b20:an20 not b20:an4
your destination is not correct I think
you need to copy values so that you do not copy formulas for the average

Absent confirming sheet2 expected output, this is my best understanding of what you want

A general piece of advice, try to avoid using select and use fully qualified ranges (see this post)



Option Explicit
Sub results()

Dim ws_in As Worksheet
Set ws_in = ThisWorkbook.Worksheets("Sheet1")
Dim ws_out As Worksheet
Set ws_out = ThisWorkbook.Worksheets("Sheet2")

Dim company As Range, average As Range
Set company = ws_in.Range("B4:AN4")
Set average = ws_in.Range("B20:AN20")

Dim r_out As Range
Set r_out = ws_out.Range("B4")

' ensure output is an empty cell catering for 0, 1, and > 1 row already existing
If Not IsEmpty(r_out.Value) Then
  If Not IsEmpty(r_out.Offset(1, 0).Value) Then
    Set r_out = r_out.End(xlDown)
  End If
  Set r_out = r_out.Offset(1, 0)
End If

company.Copy r_out
average.Copy
r_out.Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats


End Sub

Thank you for your help and great advice about avoiding "select", this is great! — user12248453, Feb 24 '21 at 22:56

score 0 · Answer 2 · answered Feb 24 '21 at 23:23

Sub CopyRangesTo()
    'Set your ranges, sepatate by coma
    Range("B4:AN4,B20:AN20").Select
    Selection.Copy
    Sheets ("yourDestinationSheetName")
    'range("A1").select ' range you will paste <--CHECK HERE
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Transferring range of data to another worksheet

2 Answers2