i adding cells multiple workbooks single workbook specifying cell in formula , adding them designated cell/column in new workbook. include loop within 'if.. then..with' statement. 'for' loop add the specified range a2:c57 each of sheets 3,4,5,6,7,8,9 cells g,h,i , on. how can add loop in current vba code?
sub getdata() dim mypath string dim filename string dim sheetname string dim n long dim newrow long mypath = "c:\attach" sheetname = "title" filename = dir(mypath & "\*.xlsx") while filename <> "" if filename <> thisworkbook.name thisworkbook.sheets("sheet1") newrow = .cells(rows.count, 1).end(xlup).row + 1 'transferring cells worksheet(2)"title" "sheet1" in thisworkbook" .range("a" & newrow) .formula = "='" & mypath & "\[" & filename & "]" & sheetname & "'!b4" .value = .value end .range("b" & newrow) .formula = "='" & mypath & "\[" & filename & "]" & sheetname & "'!b5" .value = .value end .range("c" & newrow) .formula = "='" & mypath & "\[" & filename & "]" & sheetname & "'!b6" .value = .value end .range("d" & newrow) .formula = "='" & mypath & "\[" & filename & "]" & sheetname & "'!b7" .value = .value end .range("e" & newrow) .formula = "='" & mypath & "\[" & filename & "]" & sheetname & "'!a1" .value = .value end .range("f" & newrow) .formula = "='" & mypath & "\[" & filename & "]" & sheetname & "'!a2" .value = .value end 'selects range a2:c57 sheets 3,4,5,6,7,8,9 .range("g" & newrow) dim integer, j integer, k integer k = 1 'row counter destination sheet 'loop sheets 3-9 = 3 9 'loop rows 2-57 j = 2 57 'if c not empty if wrkbooksrs.sheets(i).cells(j, 3).value <> "" 'code here add a:c on row workbook in sheet1 column g. k = k + 1'increment counter next row end if next
your explanation , pseudo code not clear. wanting add sums of values cells (a:c) in loop? comments mention columns a:c being used
so if understand correctly should able set variable sum of values
with wrkbooksrs.sheets(i) newgvalue= .cells(j, 1).value + .cells(j, 2).value + .cells(j, 3).value end 