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
New contributor
Prsmj13 Machado is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
It is not necessary to select a cell before updating the formula.
How to avoid using Select in Excel VBA
Microsoft documentation:
Range.Resize property (Excel)
Range object (Excel)
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