I don't think this is a duplicate, however I apologize if it is.
I am very new to VBA, so I was hoping to have someone look over my code and help me troubleshoot the application-defined or object-defined error I am getting on the line below:
Set aCell = ActiveSheet.Range("P10:" & Cells(i, 0).Address) 'Trying to select rows based on value in i starting with P10
Here is the full macro:
Sub freeze2()
Dim ws As Worksheet
Dim aCell As Range
Dim strg() As String
Dim count As Integer
Dim i As Integer
Dim size As Integer
size = ActiveSheet.Range("B9:B79").Find(What:=Worksheets("Email Template").Range("B5").Value) 'Looks for current fiscal week
Debug.Print size
count = 0
For i = 1 To size 'should represent current fiscal week back to first week of year, so for this week the range would be 1 to 40
Next i
ReDim numbers(size)
' Get each RED sheet
For Each ws In Worksheets
If ws.Tab.Color = 255 Then ' Find only RED tabs
Debug.Print "-----------------------------------------------------------------------"
Debug.Print "Name of Red Sheet: '" & ws.Name & "'" ' Debug...
Sheets(ws.Name).Select
Set aCell = ActiveSheet.Range("P10:" & Cells(i, 0).Address) 'Trying to select rows based on value in i starting with P10
If Not aCell Is Nothing Then
Set aCell = Range("H10") 'Taking acell and hopefully copying as values to H10
'Selection.copy
'Range("H10").Select
'Selection.PasteSpecial xlPasteValues
Else
End If
Application.CutCopyMode = False ' Unselect cell
End If
Next ws
End Sub
All I am trying to do is select a range of cells (always one column however row size will change each week) and copy those values into another column. This range is in the same place on all red sheets.
ActiveSheet.Range("P10:" & Cells(i, 0).Address)toActiveSheet.Range("P10", ActiveSheet.Cells(i,16))- Scott Cranersize +1. - Scott Craner