Excel Macro help for concatenating and copying data

J

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
205
Office Version
  1. 2019
Platform
  1. Windows
I have 3 sheets in excel in Sheet 1 there are 2000 rows and 28 columns till AB.In sheet2 I have the following data

Code: 
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]TAG POS=1 TYPE=SELECT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_ddlkhata CONTENT=%[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd3 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd4 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd2 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd3 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcurrent CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentcurrent CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd0 CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessCurent CONTENT=[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=1 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=2 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:SUBMIT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_btnsave[/TD]
[/TR]
[TR]
[TD]WAIT SECONDS=2[/TD]
[/TR]
</tbody>[/TABLE]

I need a VBA macro code to copy the above code of 32 rows to copy and paste as per my requirement.and in the first 28 lines of the code I need to concatenate the A1 B1 C1 D1 up to AB1 of Sheet 1. I have data in the excel sheet 1 up to 2000 rows, hence I need to copy the 32 lines and paste downwards and then I need to concatenate Data from Sheet 1 to Sheet 2 till A2000 B2000.....upto AB2000 in Sheet3.Example of concatenating 5 Rows of Sheet 1 into Sheet 2


sheet1.JPG


Code: 
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]TAG POS=1 TYPE=SELECT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_ddlkhata CONTENT=%1[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr CONTENT=10[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr0 CONTENT=100[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd3 CONTENT=1000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd4 CONTENT=400[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd2 CONTENT=1[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd3 CONTENT=20[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev CONTENT=300[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev0 CONTENT=50[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcurrent CONTENT=150[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr  CONTENT=10[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr0  CONTENT=100[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd  CONTENT=1000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd0  CONTENT=400[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd CONTENT=1[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd0  CONTENT=20[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd  CONTENT=300[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd0  CONTENT=50[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentcurrent  CONTENT=150[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr CONTENT=10[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr0 CONTENT=100[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd  CONTENT=1000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd0  CONTENT=400[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd CONTENT=1[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd0 CONTENT=20[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd  CONTENT=300[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd0  CONTENT=50[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessCurent  CONTENT=150[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=1 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=2 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:SUBMIT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_btnsave[/TD]
[/TR]
[TR]
[TD]WAIT SECONDS=2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=SELECT FORM=ID:aspnetForm  ATTR=ID:ctl00_ContentPlaceHolder1_ddlkhata CONTENT=%2[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr CONTENT=20[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr0 CONTENT=200[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd3 CONTENT=2000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd4 CONTENT=500[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd2 CONTENT=2[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd3 CONTENT=40[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev CONTENT=400[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev0 CONTENT=100[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcurrent CONTENT=300[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr  CONTENT=20[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr0  CONTENT=200[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd  CONTENT=2000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd0  CONTENT=500[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd CONTENT=2[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd0  CONTENT=40[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd  CONTENT=400[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd0  CONTENT=100[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentcurrent  CONTENT=300[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr CONTENT=20[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr0 CONTENT=200[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd  CONTENT=2000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd0  CONTENT=500[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd CONTENT=2[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd0 CONTENT=40[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd  CONTENT=400[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd0  CONTENT=100[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessCurent  CONTENT=300[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=1 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=2 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:SUBMIT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_btnsave[/TD]
[/TR]
[TR]
[TD]WAIT SECONDS=2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=SELECT FORM=ID:aspnetForm  ATTR=ID:ctl00_ContentPlaceHolder1_ddlkhata CONTENT=%3[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr CONTENT=30[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr0 CONTENT=300[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd3 CONTENT=3000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd4 CONTENT=600[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd2 CONTENT=3[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd3 CONTENT=60[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev CONTENT=500[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev0 CONTENT=150[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcurrent CONTENT=450[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr  CONTENT=30[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr0  CONTENT=300[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd  CONTENT=3000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd0  CONTENT=600[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd CONTENT=3[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd0  CONTENT=60[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd  CONTENT=500[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd0  CONTENT=150[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentcurrent  CONTENT=450[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr CONTENT=30[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr0 CONTENT=300[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd  CONTENT=3000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd0  CONTENT=600[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd CONTENT=3[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd0 CONTENT=60[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd  CONTENT=500[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd0  CONTENT=150[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessCurent  CONTENT=450[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=1 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=2 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:SUBMIT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_btnsave[/TD]
[/TR]
[TR]
[TD]WAIT SECONDS=2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=SELECT FORM=ID:aspnetForm  ATTR=ID:ctl00_ContentPlaceHolder1_ddlkhata CONTENT=%4[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr CONTENT=40[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr0 CONTENT=400[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd3 CONTENT=4000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd4 CONTENT=700[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd2 CONTENT=4[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd3 CONTENT=80[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev CONTENT=600[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev0 CONTENT=200[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcurrent CONTENT=600[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr  CONTENT=40[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr0  CONTENT=400[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd  CONTENT=4000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd0  CONTENT=700[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd CONTENT=4[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd0  CONTENT=80[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd  CONTENT=600[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd0  CONTENT=200[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentcurrent  CONTENT=600[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr CONTENT=40[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr0 CONTENT=400[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd  CONTENT=4000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd0  CONTENT=700[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd CONTENT=4[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd0 CONTENT=80[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd  CONTENT=600[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd0  CONTENT=200[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessCurent  CONTENT=600[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=1 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=2 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:SUBMIT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_btnsave[/TD]
[/TR]
[TR]
[TD]WAIT SECONDS=2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=SELECT FORM=ID:aspnetForm  ATTR=ID:ctl00_ContentPlaceHolder1_ddlkhata CONTENT=%5[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr CONTENT=50[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txt3yr0 CONTENT=500[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd3 CONTENT=5000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprecd4 CONTENT=800[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd2 CONTENT=5[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcd3 CONTENT=100[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev CONTENT=700[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtprcdprev0 CONTENT=250[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcurrent CONTENT=750[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr  CONTENT=50[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtRntaream3yr0  CONTENT=500[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd  CONTENT=5000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent3rdprcd0  CONTENT=800[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd CONTENT=5[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrent2ndprcd0  CONTENT=100[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd  CONTENT=700[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentprvprcd0  CONTENT=250[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtrentcurrent  CONTENT=750[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr CONTENT=50[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessM3yr0 CONTENT=500[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd  CONTENT=5000[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess3prcd0  CONTENT=800[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd CONTENT=5[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcess2prcd0  CONTENT=100[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd  CONTENT=700[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessPvsprcd0  CONTENT=250[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:TEXT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_txtcessCurent  CONTENT=750[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=1 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]ONDIALOG POS=2 BUTTON=OK CONTENT=[/TD]
[/TR]
[TR]
[TD]TAG POS=1 TYPE=INPUT:SUBMIT  FORM=ID:aspnetForm ATTR=ID:ctl00_ContentPlaceHolder1_btnsave[/TD]
[/TR]
[TR]
[TD]WAIT SECONDS=2[/TD]
[/TR]
</tbody>[/TABLE]



what should be the VBA code for the automation ?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
your requirement is not clear to me. the following codes concatenate columns 1 to 28 and puts in 29th column.
Sub join()
Dim a As Integer, b As Integer
For a = 2 To Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For b = 1 To 28
Sheets("sheet1").Cells(a, 29) = Sheets("sheet1").Cells(a, 29) + ", " + Sheets("sheet1").Cells(a, b)
Next b
Next a
MsgBox "complete"
End Sub
ravishankar
Click to expand...
 
Upvote 0
Give this a try with a copy of your workbook to see if it does what you want.

It assumes that Sheet3 exists but that any data on it can be removed.
It also assumes Sheet1 only has those 28 columns of data starting in row 1 and that Sheet2 only has one column of data like you showed, starting in cell A1.

Code: 
Sub CombineData()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long, ubb1 As Long
  
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
  b = Sheets("Sheet2").Range("A1").CurrentRegion.Value
  ubb1 = UBound(b, 1)
  ReDim c(1 To ubb1 * UBound(a), 1 To 1)
  uba2 = UBound(a, 2)
  For i = 1 To UBound(a)
    For j = 1 To ubb1
      k = k + 1
      c(k, 1) = b(j, 1)
      If j <= uba2 Then c(k, 1) = c(k, 1) & a(i, j)
    Next j
  Next i
  With Sheets("Sheet3")
    .UsedRange.ClearContents
    .Range("A1").Resize(k).Value = c
  End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA Question - VBA for validating value from multiple cells/column and fetching value another sheet's cell and pasting it another cell of sheet
Replies
2
Views
312
Kaper
Kaper
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
219
SBExcel123
S
L
  • Question Question
VBA: Dynamical Userform from Module
Replies
0
Views
890
lilcrisis
L
D
  • Question Question
Copy/Pasting rows of a table to specific cells of a separate worksheet template and exporting as a new file.
Replies
0
Views
211
DjentChicken
D
C
  • Solved
VBA Code to move Amount Due from individual sheets to a master sheet, and then copy information from the master sheet back to the individual sheets?
Replies
7
Views
387
Coyotex3
C

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top