Copying multiple formulas until last row with VBA

[johnnytominaga]

Hey guys!

I'm working on a project where an Excel file is used to as a database.
Raw data in worksheet1 is treated in worksheet2 with a bunch of formulas. These values are then copied and pasted into a CSV file to be uploaded into a website.

Currently, the database has around 7000 entries. Formulas in line 2 of Worksheet2 have been copied and pasted all the way up to line 20000 to make sure that new entries are properly formatted.

That makes the whole file hold a lot more data than would be necessary.

I've been trying to optimize it so that the VBA code identifies the last row with data on worksheet1 and uses that to add data to worksheet2.

What would be the best method to do that? Run a macro that copies and pastes or run a loop that adds formulas all the way down to the last row? Is there a way to copy all formulas in a row at once?

Additionally, I'm getting an error message, which I think is because of the way I'm inputting formulas on VBA. From what I could understand, I'll to use double quotation marks, right?

Some of the formulas are:

ColumnA=IFERROR(IF(worksheet1!B2="";"";IF(worksheet1!B2<10;CONCATENATE("0000";worksheet1!B2);IF(worksheet1!B2<100;CONCATENATE("000";worksheet1!B2);IF(worksheet1!B2<1000;CONCATENATE("00";worksheet1!B2);IF(worksheet1!B2<10000;CONCATENATE("0";worksheet1!B2);worksheet1!B2)))));"")

ColumnB=IFERROR(IF(worksheet1!B2="";"";IF(worksheet1!D2="TextA";SUBSTITUTE(worksheet1!D2;"TextA";"TextA1");worksheet1!D2));"")

ColumnC=IFERROR(IF(worksheet1!B2="";"";worksheet1!E2);"")

ColumnD=IFERROR(IF(worksheet1!B2="";"";IF(AND(V2>(TODAY()-90);V2<(TODAY()+1460));V2;IF(AND(V2<=TODAY()-90;V2>=TODAY()-365);V2+365;TODAY()+730)));"")

ColumnE=IFERROR(IF(worksheet1!B2="";"";IF(OR(AA2="N/A";AA2="";AA2=" ");"TextB";IF(AA2<(TODAY()-90);"TextC";IF(AA2<(TODAY()-30);"TextD";IF(AA2<TODAY()-7;"TextE";IF(AA2<TODAY()-2;"TextF";"TextG"))))));"")


Any help will be much appreciated.

Thanks a lot.

 

[Fluff]

How about

Sub chk()
   Dim Lr As Long
   
   Lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
   With Sheets("Sheet2")
      Range("[COLOR=#ff0000]A2:N2[/COLOR]").AutoFill Range("[COLOR=#ff0000]A2:N[/COLOR]" & Lr)
   End With
End Sub

Change ranges in red to suit

 

[johnnytominaga]

It worked perfectly. Thanks a lot.

How about

Sub chk()
   Dim Lr As Long
   
   Lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
   With Sheets("Sheet2")
      Range("[COLOR=#ff0000]A2:N2[/COLOR]").AutoFill Range("[COLOR=#ff0000]A2:N[/COLOR]" & Lr)
   End With
End Sub

Change ranges in red to suit

 

[Fluff]

Glad to help & thanks for the feedback