I've been struggling with the following code that should be easy.
It fails at line:![]()
Option Explicit Sub copydata() Const DataShtName As String = "Get Data" Const PasteShtName As String = "F-28" Dim rowNum As Integer Dim NumOfXs As Integer Dim LastCol As Integer Dim copyrow As Integer Dim RowsToCopy() As Integer Dim sht As Worksheet Dim DestSht As Worksheet NumOfXs = 0 For Each sht In ThisWorkbook.Sheets If sht.Name = PasteShtName Then Set DestSht = sht Exit For End If Next sht For Each sht In ThisWorkbook.Sheets If sht.Name = DataShtName Then LastCol = sht.Range("A1").End(xlToRight).Column For rowNum = 2 To sht.Range("A1").End(xlDown).Row If Cells(rowNum, 9) = "X" Then ReDim Preserve RowsToCopy(NumOfXs) RowsToCopy(NumOfXs) = rowNum NumOfXs = NumOfXs + 1 End If Next rowNum 'Note: I use rowNum as RowsToCopy element number instead of rowNum below copyrow = 2 If NumOfXs > 0 Then For rowNum = 0 To UBound(RowsToCopy) DestSht.Range(Cells(copyrow, 1), Cells(copyrow, LastCol)).Value = sht.Range(Cells(RowsToCopy(rowNum), 1), Cells(RowsToCopy(rowNum), LastCol)).Value copyrow = copyrow + 1 MsgBox (DestSht.Name) Next rowNum End If End If Exit For Next sht End Sub
...I get an error message using the Range method on the DestSht; however, my locals window shows DestSht as a valid worksheet.![]()
DestSht.Range(Cells(copyrow, 1), Cells(copyrow, LastCol)).Value = sht.Range(Cells(RowsToCopy(rowNum), 1), Cells(RowsToCopy(rowNum), LastCol)).Value
I also attempted:
...without success.![]()
sht.Range(Cells(RowsToCopy(rowNum), 1), Cells(RowsToCopy(rowNum), LastCol)) Copy Destination:= DestSht.Range(Cells(copyrow, 1), Cells(copyrow, LastCol))
There must be something I've been missing b/c I've been having similar problems when I help others on the forum and when I attempt to use similar code within my own projects.
Thanks in advance...
Bookmarks