Add a new customer to a list of customers in a table

[SeanHaynes95425]

I use a sheet just for gathering customer information for new customers. I use this to feed information to sheets that generate bids for the 4 services I offer. Cells B3-B7 are Named Cells.

I'd like to copy this information (above) to a list of customers in a table on a separate sheet (below). This way, when I work for a customer on future jobs and do the exact same job I can run a macro to copy information from the table back to the cells in the screenshot above. From here I can quickly generate a bid for my services at current prices, and I don't have to worry about typing in the wrong information.

So my question is: how do I copy Nicole's information to row 6, the next customer's information to rows7, etc with a macro?

[#2]

formular

=torow(B3:B7)

vba:

arr=transpose(range(b2:b7))

With Sheets("list")

r = .Range("a65536").End(3).Row + 1
.Cells(r, 1).Resize(1, 5) = arr

End With

[#3]

VBA script for Excel. Let's break it down step by step.

First, the line `arr=transpose(range(b2:b7))` is attempting to transpose the range `B2:B7`. Transposing a range means to switch its rows and columns. However, the syntax should be `Application.Transpose(.Range("B2:B7").Value)` if you are working with VBA and want to store the transposed values in the variable `arr`.

Next, the script seems to be trying to find the last used row in column A of the "list" sheet and then paste the transposed array `arr` starting from that row in the first column and extending across 5 columns.

Assuming the `transpose` part is corrected as suggested above, the code could be updated to achieve this:

```vba
Sub YourSubroutineName()
Dim arr As Variant
arr = Application.Transpose(Sheets("Sheet1").Range("B2:B7").Value)

With Sheets("list")
r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Cells(r, 1).Resize(1, UBound(arr)) = arr
End With
End Sub
```

This modified code will find the last used row in column A of the "list" sheet and then paste the transposed array `arr` starting from that row in the first column and extending across 5 columns based on the size of the `arr` array.

[#4]

@peiyezhu, this doesn't work. I adapted the code to use the name of my sheets ("New Customer" and "All Customers"), but nothing is copied to the bottom of the All Customers sheet/table --  is this code included below?

Sub Copy_To_Customer_List()

Dim arr As Variant
arr = Application.Transpose(Sheets("New Customer").Range("B3:B7").Value)

With Sheets("All Customers")
r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Cells(r, 1).Resize(1, UBound(arr)) = arr
End With

End Sub

[#5]

Re:but nothing is copied to the bottom
Does any error occur?

Sub Copy_To_Customer_List()

Dim arr As Variant
arr = Application.Transpose(Sheets("New Customer").Range("B3:B7").Value)
msgbox join(arr,",")

With Sheets("All Customers")
r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Cells(r, 1).Resize(1, UBound(arr)) = arr
End With

End Sub
Have you tried below:
arr=transpose(range(b3:b7))

With Sheets("list")

r = .Range("a65536").End(3).Row + 1
.Cells(r, 1).Resize(1, 5) = arr

End With

[#6]

Sub Copy_To_Customer_List()

Dim arr As Variant
arr = Application.Transpose(Sheets("New Customer").Range("B3:B7").Value)

With Sheets("All Customers")
r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Cells(r, 1) = arr(0)
.Cells(r, 2) = arr(1)
.Cells(r, 3) = arr(2)
End With

End Sub

[#7]

@peiyezhu the code works, but I've changed things around a little.

First, I changed the "New Customer" sheet so there is no need to transpose cells. I like the data entry cells (B3-B11) to be spaced a bit so it's harder to accidentally tap on the incorrect cell when typing on my Surface Go's screen.

Second, for the "All Customers" sheet I've add a column named "Cust No" (customer number) so I can use use XLOOKUP to return a customer based on the customer number. Since customer numbers exist before new customers are added, the code adds a new customer to the first empty row after customer number 1000 (B1004), which is almost what I want. What code do I need to have a new customer (i.e. Nicole Blackwood) added to B5:F5, the next customer to C5:F5 and so on?

[#8]

@SeanHaynes 

@peiyezhu the code works, but I've changed things around a little.

First, I changed the "New Customer" sheet so there is no need to transpose cells.

What do you mean no need to transpose cells?

All by hard coding like below:

arr[0]=[B3]

arr[1]=[B5]

arr[2]=[B7]

....

That is OK but not elegant for further maintain.

I like the data entry cells (B3-B11) to be spaced a bit

You can set a bigger number size to the height of each row rather than insert a blank row.

so it's harder to accidentally tap on the incorrect cell when typing on my Surface Go's screen.

What code do I need to have a new customer (i.e. Nicole Blackwood) added to B5:F5, 

Sub Copy_To_Customer_List()

Dim arr As Variant
arr = Application.Transpose(Sheets("New Customer").Range("B3:B7").Value)
msgbox join(arr,",")

With Sheets("All Customers")

'here changed to last row according to column B
'r = .Range("A" & .Rows.Count).End(xlUp).Row + 1

r=.Range("B" & .Rows.Count).End(xlUp).Row + 1
'.Cells(r, 1).Resize(1, UBound(arr)) = arr

'fill start from B column

.Cells(r, 2).Resize(1, UBound(arr)) = arr
End With

End Sub

[#9]

That solved the problem, thanks!

[#10]

You are welcome.

[#11]

I've done some work to make my spreadsheet easier to use and read. I'd like to change:

arr = Application.Transpose(Sheets("New Customer").Range("A29:A53").Value)

to refer to Sheets("New Customer").Range("B15:AG15")

thus eliminating the need for the Transpose function.

I just don't understand VBA enough to make this change.

[#12]

That is true. You got it.

[#13]

@peiyezhu 

The reason for using `Application.Transpose` in this context is to effectively convert a column range into a row range. By default, the array created from a column range is vertical (one column wide and multiple rows tall). However, when you want to paste this data into a row horizontally, you need to transpose the array so that it becomes horizontal (one row wide and multiple columns tall).

In this code, `arr = Application.Transpose(Sheets("New Customer").Range("B3:B7").Value)` is transposing the column range "B3:B7" from the "New Customer" sheet into a horizontal array. This allows the data to be pasted into the "All Customers" sheet as a row in the specified location.

The use of `Application.Transpose` is a common technique to manipulate array orientation in VBA, especially when dealing with transferring data between rows and columns.

[#14]

Whoops, I guess I wasn't clear. The data are in Sheets("New Customer").Range("B15:AG15").

How do I add these cells to the array?

[#15]

@peiyezhuSince adding your code to my spreadsheet, I have learned that it's handy to have the name, address, email, etc for multiple new customers on hand. However, I prefer to have this data in rows instead of columns so it's much easier to copy data from the Selected New Customer to my list of all customers. As it stands now, I have cell formulas to copy data from my list of 10 new customers, to a column on the left that is the source for data to be copied to my list of all customers (red arrows). What I'd like is a macro that copies name, address, city, email, and mileage to the Selected New Customer row (green arrow) where it is matched up with the other details from elsewhere in the spreadsheet. This way I don't have the awkwardness of some data being vertical and other data being horizontal.

[#16]

@SeanHaynes 

However, I prefer to have this data in rows instead of columns so it's much easier to copy data from the Selected New Customer to my list of all customers. 

Sorry,I can not understand which one is the destiantion table.

why does List of all custermers list all items in multiple rows rather than columns?

In former threads,the new customer has been list in multiple rows.

We have tried to transpose them save to columns in a row.

Can you share your source table and expected result?

G15 merge cell Panes?

I15 merge cell External Screen?

If you use merge cell,it is not easy for further calculation or analysis.

When you save your data, please  refer the source table layout for a pivtot table.

[#17]

@peiyezhu 

New customers are added to the New Customer sheet. I then enter window washing or solar panel cleaning details on their respective worksheets. Once that is done I come back to the New Customer sheet and click on a button to run a macro copy the new customer's details to the All Customers sheet, name, address...to number of "Hard" solar panels.

How do I do this with all data in 1 row? 

[#18]

@SeanHaynes 

So, how do I get "arr" to refer to B18:M18 so I add a new customer and their window/solar details to my list of All Customers?

B18:M18?

G18:M18 ALL Empty.

Why refer to blank cells (G18:M18)

their window/solar?

Window Tally Sheet has only one row data besides first header row.

Sheet solar too.

What do you mean their window/solar?