Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Store data from another tab in Excel - next_row

0

Hi all,

I am using the following tutorial from you: https://www.teachexcel.com/excel-tutorial/find-the-next-blank-row-with-vba-macros-in-excel_1261.html 

Now I have the problem that Excel puts the data at the bottom of the table (output) and not at the top of the first (blank) row. Why is this?

Sub Invoice_input()

ws_output = "Invoicing"

next_row = Sheets(ws_output).Range("C" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("PO_Reference1").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("POnumber").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("Actual_shipping_date").Value
Sheets(ws_output).Cells(next_row, 11).Value = Range("Client_name").Value
Sheets(ws_output).Cells(next_row, 10).Value = Range("Client_number").Value
Sheets(ws_output).Cells(next_row, 12).Value = Range("Destination_country").Value
Sheets(ws_output).Cells(next_row, 13).Value = Range("Product_category").Value
Sheets(ws_output).Cells(next_row, 14).Value = Range("Product_description1").Value
Sheets(ws_output).Cells(next_row, 15).Value = Range("Product_price1").Value
Sheets(ws_output).Cells(next_row, 16).Value = Range("Product_description2").Value
Sheets(ws_output).Cells(next_row, 17).Value = Range("Product_price2").Value
Sheets(ws_output).Cells(next_row, 18).Value = Range("Product_description3").Value
Sheets(ws_output).Cells(next_row, 19).Value = Range("Product_price3").Value
Sheets(ws_output).Cells(next_row, 20).Value = Range("Price2").Value
Sheets(ws_output).Cells(next_row, 21).Value = Range("BTW").Value
Sheets(ws_output).Cells(next_row, 22).Value = Range("Priceexbtw").Value
Sheets(ws_output).Cells(next_row, 23).Value = Range("VAT_percentage").Value
Sheets(ws_output).Cells(next_row, 24).Value = Range("Invoice_description2").Value

MsgBox "Invoice data has been created. You can find this in the Invoicing tab." 
End Sub  
excel
MS Office Version: 2019
posted by: thijs on Mon Feb 8, 2021 at 7:37 am
Answer
Discuss

Answers

0

Hi Thijs and welcome to the Forum

The point of Don's tutorial is to add rows AFTER the existing data (i.e. newest data is at the bottom of the data on the sheet (a very common approach).

If you want to copy data into a given row  at the top, then you don't need that approach. If your worksheet contains "regular" cells (i.e. you don't have an Excel table) then this should work... 

I've modified (only part) of your code below to suggest the right approach. The first bold line inserts a new row at row 2 (assuming row 1 contains headers, but you can change the number on brackets to suit) and copies any formatting form what was row 2 (i.e. earlier data). Then you should copy data into new Cells (2, x) not Cells(nextRow, x) in the lines that follow...

Sub Invoice_input()
ws_output = "Invoicing"
Sheets(ws_output).Rows(2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Sheets(ws_output).Cells(2, 1).Value = Range("PO_Reference1").Value
Sheets(ws_output).Cells(2, 8).Value = Range("POnumber").Value
If however you do have a table then you'd need to add a (single) new row to the first data row of your table using this (assuming there's ony one table in that sheet):
Set MyNewRow = Sheets(ws_output)ListObjects(1).ListRows.Add(1)
.

Hope this helps.

posted by: John_Ru (rep: 6142) on Mon Feb 8, 2021 at 9:37 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login