How do we remake the program to copy not one value value but with range in column age?

%3CLINGO-SUB%20id%3D%22lingo-sub-2297310%22%20slang%3D%22en-US%22%3EHow%20do%20we%20remake%20the%20program%20to%20copy%20not%20one%20value%20value%20but%20with%20range%20in%20column%20age%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2297310%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22votecell%20post-layout--left%22%3E%3CDIV%20class%3D%22js-voting-container%20grid%20jc-center%20fd-column%20ai-stretch%20gs4%20fc-black-200%22%3E%3CDIV%20class%3D%22js-vote-count%20grid--cell%20fc-black-500%20fs-title%20grid%20fd-column%20ai-center%22%3E0%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22postcell%20post-layout--right%22%3E%3CDIV%20class%3D%22s-prose%20js-post-body%22%3E%3CP%3EHow%20do%20we%20remake%20the%20program%20to%20copy%20not%20one%20value%20value%20but%20with%20range%20in%20column%20age%3F%20We%20need%20copy%20all%20values%20from%20age.xlsm%20age%20column%20to%20another%20age%20column%20in%20shchool.xlsm%20file%3C%2FP%3E%3CP%3Eschool.xlsm%3C%2FP%3E%3CDIV%20class%3D%22s-table-container%22%3EA%20B%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Ename%3C%2FTD%3E%3CTD%3Eage%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epeter1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ejane2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eted%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epeter3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CPRE%3Eage.xlsm%3C%2FPRE%3E%3CDIV%20class%3D%22s-table-container%22%3EA%20B%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Ename%3C%2FTD%3E%3CTD%3Eage%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Emark3%3C%2FTD%3E%3CTD%3E1%2C2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epeter1%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ejane2%3C%2FTD%3E%3CTD%3E1%2C5%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CP%3Ethe%20program%20copy%20one%20value%2C%20how%20do%20we%20copy%20all%20values%20from%20age%20column%20from%20age.xlsm%20to%20shool.xlsm%20file%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22hljs-keyword%22%3ESub%3C%2FSPAN%3E%20copydata()%0A%3CSPAN%20class%3D%22hljs-keyword%22%3EDim%3C%2FSPAN%3E%20iAge%20%3CSPAN%20class%3D%22hljs-keyword%22%3EAs%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-type%22%3EInteger%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22hljs-keyword%22%3ESet%3C%2FSPAN%3E%20src%3DWorkbooks.Open(%3CSPAN%20class%3D%22hljs-string%22%3E%22age.xlsm%22%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22hljs-literal%22%3ETrue%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22hljs-literal%22%3ETrue%3C%2FSPAN%3E)%0AiAge%20%3D%20src.Worksheets(%3CSPAN%20class%3D%22hljs-string%22%3E%22Sheet1%22%3C%2FSPAN%3E).Range(%3CSPAN%20class%3D%22hljs-string%22%3E%22B2%22%3C%2FSPAN%3E).Value%0A%20ThisWorkbook.Worksheets(%3CSPAN%20class%3D%22hljs-string%22%3E%22Sheet1%22%3C%2FSPAN%3E).Range(%3CSPAN%20class%3D%22hljs-string%22%3E%22%D0%922%22%3C%2FSPAN%3E).Value%20%3D%20iAge%0A%3CSPAN%20class%3D%22hljs-keyword%22%3EEnd%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-keyword%22%3ESub%3C%2FSPAN%3E%3C%2FPRE%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2297310%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2302354%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20we%20remake%20the%20program%20to%20copy%20not%20one%20value%20value%20but%20with%20range%20in%20column%20age%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2302354%22%20slang%3D%22en-US%22%3Enew%20version%20why%20program%20does%20not%20work%20Run-time%20error%20'1004'%20application-defined%20or%20object%20defined%20error%20on%20line%20If%20Cells(d%2C%20%22A%22)%20%3D%20iName%20Then%20Sub%20Insertdata()%20Dim%20iAge%20As%20Integer%20Set%20src%3DWorkbooks.Open(%22D%3A%5Cschool1%5Cage.xlsm%22%2C%20True%2C%20True)%20t%20%3D%202%20lastrow%20%3D%20ActiveSheet.UsedRange.Rows.Count%20Do%20Until%20t%20%3D%20lastrow%20iAge%20%3D%20src.Worksheets(%22Sheet1%22).Range(%22B%22%20%26amp%3B%20t).Value%20IName%20%3D%20src.Worksheets(%22Sheet1%22).Range(%22A%22%20%26amp%3B%20t).Value%20Do%20Until%20d%20%3D%20lastrow%20If%20Cells(d%2C%20%22A%22)%20%3D%20IName%20Then%20ThisWorkbook.Worksheets(%22Sheet1%22).Range(%22B%22%20%26amp%3B%20d).Value%20%3D%20iAge%20d%20%3D%20d%20%2B%201%20End%20If%20Loop%20t%20%3D%20t%20%2B%201%20Loop%20End%20Sub%3C%2FLINGO-BODY%3E
Occasional Contributor
0

How do we remake the program to copy not one value value but with range in column age? We need copy all values from age.xlsm age column to another age column in shchool.xlsm file

school.xlsm

A B
nameage
peter1 
jane2 
ted 
peter3 
age.xlsm
A B
nameage
mark31,2
peter15
jane21,5

the program copy one value, how do we copy all values from age column from age.xlsm to shool.xlsm file

Sub copydata()
Dim iAge As Integer
Set src=Workbooks.Open("age.xlsm", True, True)
iAge = src.Worksheets("Sheet1").Range("B2").Value
 ThisWorkbook.Worksheets("Sheet1").Range("В2").Value = iAge
End Sub
2 Replies
new version why program does not work Run-time error '1004' application-defined or object defined error on line If Cells(d, "A") = iName Then Sub Insertdata() Dim iAge As Integer Set src=Workbooks.Open("D:\school1\age.xlsm", True, True) t = 2 lastrow = ActiveSheet.UsedRange.Rows.Count Do Until t = lastrow iAge = src.Worksheets("Sheet1").Range("B" & t).Value IName = src.Worksheets("Sheet1").Range("A" & t).Value Do Until d = lastrow If Cells(d, "A") = IName Then ThisWorkbook.Worksheets("Sheet1").Range("B" & d).Value = iAge d = d + 1 End If Loop t = t + 1 Loop End Sub
why my program stall with no output?


Option Explicit
Sub Insertdata()
Dim src As Workbook
Dim iAge As Integer
Dim t As Integer
Dim d As Integer
Dim lastrow As Integer
Dim iName As String
Set src=Workbooks.Open("D:\school1\age.xlsm", True, True)
lastrow = ActiveSheet.UsedRange.Rows.Count
t = 2
d = 2
Do Until t = lastrow
iAge = src.Worksheets("Sheet1").Range("B" & t).Value
iName = src.Worksheets("Sheet1").Range("A" & t).Value
Do Until d = lastrow
If Cells(d, "A") = iName Then
ThisWorkbook.Worksheets("Sheet1").Range("B" & d).Value = iAge
d = d + 1
End If
Loop

t = t + 1
Loop
End Sub

The program copy all values from age.xlsm age column to another age column in shchool.xlsm file according to name column. Name is key in tables.

school.xlsm

|A | B |
|:---- |:------:|
|name | age|
|peter1 | |
|jane2 | |
|ted | |
|peter3 | |

age.xlsm

|A |B|
|:---- |:------:|
|name |age|
|mark3| 1,2|
|peter1 | 5 |
|jane2 | 1,5|