Halley yenn
New Member
- Joined
- Mar 17, 2021
- Messages
- 32
- Office Version
- 365
- Platform
- Windows
Hello,
I am trying to fill a excel template where the data starts from Row6, there are many columns but I have to fill only 4 columns data from another workbook.
I used the below code but the problem is it is entering the data correctly but overwriting the same cells I wonder maybe I am doing something wrong.
Could you please help me on this.
Dim mb,inputrep As workbook
Dim mbsh, inputrep As worksheet
Dim xlsRange As Excel.Range
Dim OrderID, OrderName, OrderRef As String
Dim wname As String
Set mb = ThisWorkbook
Set mbsh = mb.Sheets("Settings")
wname = mbsh.Range("B1").Value
templatewbname = mbsh.Range("B2").Value
Set templatewb = Workbooks.Open(templatewbname, UpdateLinks:=False)
Set templatesh = templatewb.Sheets("Order")
templatesh.Activate
Set Inputrep = Workbooks.Open(wname, UpdateLinks:=False)
Set Inputrepsh = Inputrep.Sheets("Input")
rowcount = Inputrepsh .Cells(Rows.Count,1).End(xlUp).Row
For i = 2 to rowcount
validrecord = "false"
If UCase(Inputrepsh .Range("B" & I).Value) <> "" Then
Validrecord = "true"
OrderID = Inputrepsh.Range("B" & i).Value
OrderName = Inputrepsh.Range("C" & i).Value
OrderRef= Inputrepsh.Range("D" & i).Value
End If
If Validrecord = "true" Then
templaterowcount = templatesh .Cells(Rows.Count,1).End(xlUp).Row
templatesh.Range("D" & templaterowcount + 1).Value = OrderID
templatesh.Range("H" & templaterowcount + 1).Value = OrderName
templatesh.Range("P" & templaterowcount + 1).Value = OrderRef
End If
Next i
Inputrep.save
Inputrep.Close
Note: Here,Inputrep is the input file where data should be taken from colB,C,D
and templatesh one is the output template where I have to fill the above B,C,D in Col D,H,P
Kr,
Halley
I am trying to fill a excel template where the data starts from Row6, there are many columns but I have to fill only 4 columns data from another workbook.
I used the below code but the problem is it is entering the data correctly but overwriting the same cells I wonder maybe I am doing something wrong.
Could you please help me on this.
Dim mb,inputrep As workbook
Dim mbsh, inputrep As worksheet
Dim xlsRange As Excel.Range
Dim OrderID, OrderName, OrderRef As String
Dim wname As String
Set mb = ThisWorkbook
Set mbsh = mb.Sheets("Settings")
wname = mbsh.Range("B1").Value
templatewbname = mbsh.Range("B2").Value
Set templatewb = Workbooks.Open(templatewbname, UpdateLinks:=False)
Set templatesh = templatewb.Sheets("Order")
templatesh.Activate
Set Inputrep = Workbooks.Open(wname, UpdateLinks:=False)
Set Inputrepsh = Inputrep.Sheets("Input")
rowcount = Inputrepsh .Cells(Rows.Count,1).End(xlUp).Row
For i = 2 to rowcount
validrecord = "false"
If UCase(Inputrepsh .Range("B" & I).Value) <> "" Then
Validrecord = "true"
OrderID = Inputrepsh.Range("B" & i).Value
OrderName = Inputrepsh.Range("C" & i).Value
OrderRef= Inputrepsh.Range("D" & i).Value
End If
If Validrecord = "true" Then
templaterowcount = templatesh .Cells(Rows.Count,1).End(xlUp).Row
templatesh.Range("D" & templaterowcount + 1).Value = OrderID
templatesh.Range("H" & templaterowcount + 1).Value = OrderName
templatesh.Range("P" & templaterowcount + 1).Value = OrderRef
End If
Next i
Inputrep.save
Inputrep.Close
Note: Here,Inputrep is the input file where data should be taken from colB,C,D
and templatesh one is the output template where I have to fill the above B,C,D in Col D,H,P
Kr,
Halley