0

I have a macro that takes an input and finds a sheet with a matching name. From there it loops through the first row (headers) to find the column that matches a second input.

I want to copy that entire column and paste it onto a different sheet.

When I run the code, it works until the copy/ paste where I get

"Run-time error '1004' Application-defined or object-defined error

noted with a comment.

SDay = Sheets("Heat Map").Range("C1").Value
STime = Sheets("Heat Map").Range("C2").Value

Worksheets(SDay).Activate

For i = 2 To 17
    If ActiveSheet.Cells(1, i).Value = Sheets("Heat Map").Range("C2").Value Then
        ActiveSheet.Range(Cells(2, i), Cells(19, i)).Select ' Error is here
        Selection.Copy
    End If
Next i

Sheets("Heat Map").Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Heat Map").Activate
Community
  • 1
  • 1
Liworker
  • 37
  • 5

1 Answers1

0

It's a common mistake but you shouldn't use copy/paste or activate or activesheet. This has been discussed thousands of times. Check this great answer on how to avoid it: How to avoid using Select in Excel VBA

The main reason is you never now what "ActiveSheet" is. Imagine if for some reason the sheet couldn't be activated. You'd be destroying the rest of the workbook by pasting something where it shouldn't go. Always create workbook and worksheets objects and work with them.

Anyway, try assigning one range to the other. Just make sure both have the same dimensions (length, width). So instead of copy and then paste just do something like

Dim heatMap as Worksheet

Set heatMap = ThisWorkbook.Worksheets("Heat Map")
Set otherSheet = ThisWorkbook.Worksheets("Your other sheet")

otherSheet.Range(otherSheet.Cells(2, i), otherSheet.Cells(19, i))  = heatMap.Range(heatMap.Cells(2, i), heatMap.Cells(19, i))

I didn't test this code so if it fails try going slower by first attempting to copy ranges of single cells (with hard-coded rows and columns) and then working your way up from there.

baka_toroi
  • 76
  • 6