I want to to automate the data collection of some cells of the sheet NomeSheet to fil some cells of sheet DATA, starting at row 45, collecting data for cells E45, I45, M45 and Q45 up to row 64, so I tried using for as show below, but it's not working. Data to be collected are in the cells D47 to D50 of the NomeSheet sheet. Basically I need to include the variable m in the name of the cells. Can anyone help?
Sub NOVOS_GRAFICOS_FINAL()
' NOVOS_GRAFICOS Macro
' Keyboard Shortcut: Ctrl+j
Dim m As Integer
Dim NomeSheet, EE As String
NomeSheet = InputBox("Enter the name of the Sheet")
Sheets("DATA").Select
For m = 45 To 64
Range("E & m &").Select
Worksheets("DATA").Range("E & m &").Formula = "='" & NomeSheet & "'!$D$47"
Range("I & m &").Select
Worksheets("DATA").Range("I & m &").Formula = "='" & NomeSheet & "'!$D$48"
Range("M & m &").Select
Worksheets("DATA").Range("M & m &").Formula = "='" & NomeSheet & "'!$D$49"
Range("Q & m &").Select
Worksheets("DATA").Range("Q & m &").Formula = "='" & NomeSheet & "'!$D$50"
Next
End Sub
It is not necessary to select a cell before updating the formula.
How to avoid using Select in Excel VBA
Microsoft documentation:
Pls try
Sub NOVOS_GRAFICOS_FINAL()
Dim m As Integer
Dim NomeSheet, EE As String
NomeSheet = InputBox("Enter the name of the Sheet")
With Worksheets("DATA")
For m = 45 To 64
.Range("E" & m).Formula = "='" & NomeSheet & "'!$D$47"
' or using Cells(row#, col#/index)
' .Cells(m, "E").Formula = "='" & NomeSheet & "'!$D$47"
.Range("I" & m).Formula = "='" & NomeSheet & "'!$D$48"
.Range("M" & m).Formula = "='" & NomeSheet & "'!$D$49"
.Range("Q" & m).Formula = "='" & NomeSheet & "'!$D$50"
Next
End With
End Sub
OR
Sub NOVOS_GRAFICOS_FINAL1()
Dim m As Integer
Dim NomeSheet, EE As String
NomeSheet = InputBox("Enter the name of the Sheet")
With Worksheets("DATA")
.Range("E" & 45).Resize(20).Formula = "='" & NomeSheet & "'!$D$47"
.Range("I" & 45).Resize(20).Formula = "='" & NomeSheet & "'!$D$48"
.Range("M" & 45).Resize(20).Formula = "='" & NomeSheet & "'!$D$49"
.Range("Q" & 45).Resize(20).Formula = "='" & NomeSheet & "'!$D$50"
End With
End Sub