Forum Discussion

sergey989's avatar
sergey989
Brass Contributor
Apr 27, 2021

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

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
excel
Macros and VBA

2 Replies

  • sergey989's avatar
    sergey989
    Brass Contributor
    Apr 28, 2021
    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
    • sergey989's avatar
      sergey989
      Brass Contributor
      Apr 29, 2021
      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|





Resources