CODEWITHSUNDEEP
vbaexcel
A Cohen
A Cohen

Reputation: 456

VBA - Simplified copy and paste Macros

I am having difficulties simplifying my macros that copies and pastes data into various sheets. 

'Put the date and time across the top
    Dim rngDT As Range
    Set rngDT = Worksheets("Data").Range("A2:B2")
    Worksheets("Data").Range(rngDT, rngDT.End(xlDown)).Copy
    Worksheets("Reporting").Range("C5").Offset((x - 1) * 12, 0).PasteSpecial _
    Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'Copy and transpose the names from the names page
    Worksheets("Point Names").Range("B1:B3").Offset(x, 0).Copy _
    (Worksheets("Reporting").Range("B7").Offset((x - 1) * 12, 0))     

'Copy and transpose the data
    Dim dataRng As Range
    Set dataRng = Worksheets("Data").Range("C1:E1").Offset(1, 3 * x - 3) '.Range("A1:C1")
    Worksheets("Data").Range(dataRng, dataRng.End(xlDown)).Copy
    Worksheets("Reporting").Range("C7").Offset((x - 1) * 12, 0).PasteSpecial _
    Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    'Sheets("Data").Select
    'Range("A1").Select
    'ActiveCell.Offset(1, x + 1).Range("A1:C1").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.Copy
    'Sheets("Reporting").Select
    'Range("C7").Offset((x - 1) * 12, 0).Select
    'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

Also for reference, it loops For x = 1 To NumPoints which NumPoints = 33 in this example. Thank you in advance, any help will be greatly appreciated

Upvotes: 0

Views: 253

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

The first code block can be reduced to something like the following:

'Put the date and time across the top
Dim rngStart As Range
Set rngStart = Worksheets("Data").Range("A2:B2")

Worksheets("Data").Range(rngStart, rngStart.End(xlDown)).Copy
Worksheets("Reporting").Range("C5").Offset((x - 1) * 12, 0).PasteSpecial Paste:=xlPasteAll, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=True

The rest of the code can be reduced in the same way, give it a try.
Always try to avoid using .Select or Selection. which is bad practice.

Note that I used Worksheets instead of Sheets because the Worksheets object only contains worksheets but the Sheets object also contains worksheets and charts and so on. I recommend always to use Worksheets over Sheets unless you really need Sheets, which in most cases you do not.

Also make sure that every Range, Columns, Rows, Cells and so on is prefixed by a valid named worksheet. If not VBA assumes that you meant the ActiveSheet. That means:

Range(rngStart, rngStart.End(xlDown)).Copy

is exactly the same as 

ActiveSheet.Range(rngStart, rngStart.End(xlDown)).Copy

But ActiveSheet is not a defined sheet. Therefore there might be a chance that the active sheet is the right one, but there is also the chance that it is not. So you get a randomly working/non-working code.

Therefore always use a defined worksheet like

Worksheets("Data").Range(rngStart, rngStart.End(xlDown)).Copy

and never let VBA assume the worksheet.

Edit to your comment. This would be something like this:

Dim dataRng As Range
Set dataRng = Worksheets("Data").Range("A1").Offset(1, x + 1).Range("A1:C1")
Worksheets("Data").Range(dataRng, dataRng.End(xlDown)).Copy
Worksheets("Reporting").Range("C7").Offset((x - 1) * 12, 0).PasteSpecial Paste:=xlPasteAll, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Upvotes: 2

Related Questions