VBA code to take number from a column put it in another sheet and update to populate value

[Jo1986]

Hi there,
I've got a big file with a large dataset. One of the sheets works as a screening for the data, so everytime I populate an ID number in say cell C3, the rest of the sheet updates as per the formulas I have in place.
However, I need to write a code that takes ID numbers one by one from a sheet, populates them into my screening sheet, updates the sheet and saves it.
Can anyone guide me on what code to use?

Thank you

[rcm]

yes, but it would be easier if you could provide a sample (non sensitive) on the data along with the desired results

[Jo1986]

Thanks.
Say in Sheet 1 - Cells A1:A50 have ID numbers.
Sheet 2: When I write any of the ID numbers in Sheet2!C3 the rest of the sheet updates. That is all fine and automated now.
The vba code needs to take each number from A1:A50 put it in C3, update sheet and save.

[rcm]

So there are going to be 50 different results and only the last one is going to be saved ..

Please Login or Register  to view this content.

[Jo1986]

Thanks - I managed to get the code up and running however, there is one error. See my code below. The loop should:

1. Take the "nameFile" (which is the ID number) and put it to "D8"
2. Then link This workbook F6, F16, F26 to the corresponding values in worksheets "LoniCommentary" of the file specified in namefile "D8".

My code has everything in place, however, it only links the first file correctly, the corresponding ones are linked to the first ID(Namefile) and not the subsequent ones as specified in D8.

Additionally, I need to add a command to close the files opened (easy question, I know!)

Sub singleco()
'
' singleconnection Macro
'
Dim i As Integer
Dim numRows As Integer
Dim nameFile As String
Dim ws As Worksheet

strTemplatePath = "H:\J\VBA summary\RenameTest\" & nameFile
strTemplateFileName = Dir$(strTemplatePath & "*.xlsx")


numRows = Application.CountA(ThisWorkbook.Sheets("Sheet2").[A:A])

For i = 1 To numRows
nameFile = ThisWorkbook.Sheets("Sheet2").Cells(i, 1).Value

Workbooks.Open Filename:=strTemplatePath & nameFile, UpdateLinks:=False_

Set wsChecklist = ActiveWorkbook.Sheets("Checklist")
Set wsLoniCommentary = ActiveWorkbook.Sheets("LoniCommentary")
Set wsProperCommentary = ActiveWorkbook.Sheets("PoperCommentary")


ThisWorkbook.Sheets("Single Connection").[D8].Value = nameFile
ThisWorkbook.Sheets("Single Connection").Calculate

[F6].Value = Workbooks(nameFile).Worksheets("LoniCommentary").[D6].Value

[F16].Value = Workbooks(nameFile).Worksheets("LoniCommentary").[D14].Value

[F26].Value = Workbooks(nameFile).Worksheets("LoniCommentary").[D22].Value

ChDir "H:\J\VBA summary"
ThisWorkbook.SaveAs Filename:=nameFile & ".xlsm"


Next i

End Sub



Thank you very much for your help in advance.