Excel Macro help for concatenating and copying data

J

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
204
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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

L
  • Question
VBA: Dynamical Userform from Module
Replies
0
Views
642
lilcrisis
L
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
399
Peter_SSs
Peter_SSs
C
  • Question
Transposing Data from two columns with a common data identifier into another column one row at a time
Replies
4
Views
632
Fluff
Fluff
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
287
madi1004
M
P
  • Question
VBA for sending rows of data if column A = current date. (Via email)
Replies
1
Views
395
paun_shotts
P

Forum statistics

Threads
1,215,064
Messages
6,122,941
Members
449,094
Latest member
teemeren

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