VBA code copy data from cell to another sheet in table

[vpseuro]

Dear all,
I`m having trouble in creating a correct VBA code for a copy paste function.
Main sheet "home"
Second sheet "clients"

On "home" sheet i have 4 cells ( C column - "C4:C7") representing data: id nr, name from a dropdown, name entered manually , and a date.
This data should be copied in the second "clients" sheet in a table "clientstable" header starting on row 5, first table row should be 6.

i made progress but the paste entry goes to row 7 and not in the table,
second progress , data goes in the table but fills the entire table and not specific row by row as i need it.

the goal is that data should go from one cell C4 , c5 , c6 ,c7 home sheet to A6 B6 C6 D6 clients sheet in table clientstable to next row in line available

Sub InsertNewclient()
'
' Newclient Macro
'
'
Dim countrow As Long
Dim countcol As Long
Dim i As Integer
 Dim CurrentSheet As Object


countrow = Sheets("Clients").UsedRange.Rows.Count
countcol = Sheets("Clients").UsedRange.Columns.Count

 
    Sheets("Home").Select 
    Range("C4:C7").Select
  
    Selection.Copy
    Sheets("Clients").Select

    Range("Clientstable").Select
  
    Cells(Rows.Count, 1).End(xlUp)(1).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
  
          
End Sub

 

[johnnyL]

?
Sheets("Home").Range("C4:C7").Copy Sheets("Clients").Range("A6:D6")

 

[maabadi]

For One Record Try This:

Sheets("Clients").Range("A6:D6") = Application.Transpose(Sheets("Home").Range("C4:C7"))

if You want Repeat it for New Records one by One:

Sub AddData()
Dim Lr As Long
Lr = Sheets("Clients").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Clients").Range("A" & Lr & ":D" & Lr) = Application.Transpose(Sheets("Home").Range("C4:C7"))
End Sub

 

[vpseuro]

Dear @johnnyL and @maabadi

Thank you for the help , I implemented the one below. The thing as in previous cases/attempts with various approaches I could not succeed to add data to the actual table in the first row.

Tablename is Clientstable with four columns A:D in this case as described in the post.

Sub AddData()
Dim Lr As Long
Lr = Sheets("Clients").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Clients").Range("A" & Lr & ":D" & Lr) = Application.Transpose(Sheets("Home").Range("C4:C7"))
End Sub

Can we use this and target the table?
or do we need to use specific .Range for every cell?

thank you

 

[vpseuro]

Hi guys,
All formulas i have tried are actually checking the first available row and recognises the first empty row in my table as having data.
Nevertheless that first row has validated data or conditional formatting used from copy.

it seems that a solution would be to enter some IF clause to check if table is available under the name "Clientstable" and if not to be created then to enter data in first row.
i can`t find a solution or code to focus/target an already created table and copy / replace the data starting from row 6 and continue to next row in line.

 

[maabadi]

1. You want to add data at Row 6 ( first row after header)?
2. Then Are you want Insert Row and then first row will be empty for new record or Not?
Please Upload your Table with Xl2BB to we know to correct Structure of Your Table.

 

[vpseuro]

once again thank you for your effort and time

this is my testing file

testinsertdatatotablemacro.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4		Order ID	1
5		Entry by	Manager 2
6		Client Name	hfhhfhfh
7		Entry date	09/09/2021 18:05
8
9
10
11
Home

Cells with Data Validation
Cell	Allow	Criteria
C5	List	=data!$A$2:$A$4

testinsertdatatotablemacro.xlsm
	A	B	C	D	E
1
2
3	C4	C5	C6	C7
4
5	Order ID	Entry by	Client name	Entry Date
6
7	1	Manager 2	hfhhfhfh	44448.75381
8
9
10
11
Clients

testinsertdatatotablemacro.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4	C4	C6				C7				C2
5
6	Order ID	Client name	Column4	Column3	Column1	Entry Date	Due Date	Time	Column2	Entry by
7	1	hfhhfhfh				09/09/2021 18:05		#VALUE!		Manager 2
8
9
10
11
12
13
Clientstargetcollumn

Cell Formulas
Range	Formula
H7	H7	=TEXT(G7-F7, "dd:hh:mm")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J6	Cell Value	contains "YES"	text	NO
E6	Cell Value	contains "Critical"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J7	List	=data!$A$2:$A$4

testinsertdatatotablemacro.xlsm
	A	B
1	Choose name
2	Manager 1
3	Manager 2
4	Manager 3
5
data

 

[maabadi]

1. Are you want to add Data at 2nd Table Uploaded or 3rd?
2. if 2nd you want add data at row 6?
3. If you want 3rd Table, you want to add data to Column A, J , B , & F respectively?
4. Are you have unique ID? Can We use it to fid that data exist at new Table or Not?

 

[vpseuro]

two cases / two macro

1. case C4:C7 from Home to go in Clients sheet / table Clientstable and every next entry to have a new row, the row can always be first or last , i assume it does not matter much i can sort it by ID .
2. case the C4:C7 from Home i have to put them in Clientstargetcollumn sheet in specific cell also a new row should be created in that table.


answer to your question
1: one macro will add data to second sheet called Clients in the table Clientstable another macro will add data to third sheet in table Clienttargetcollumn
2.the first macro should add data to second sheet that table Clintstable starting from first row of the table in this case row 6 , i assume it will be a transpose function so we can keep the formatting, formulas, data value etc., i managed to add data to the table wen i have first row with data inserted but the case will be that new file will be opened and table should be built with data from beginning in our case row 1 of the table. after entering one row of data , next macro run should create second row of data and so on..
3. second macro correct - to add data to Column A, J , B , & F respectively , same issue above with first row propagating in the table.

4. not sure of the unique ID - tables are named Clientstable and Clienttargetcollumn

 

[maabadi]

Try this for Both without searching for existing Data at Tables:

Sub AddData()
Dim Lr As Long, Ar1() As Variant, Ar2() As Variant, Lr2 As Long
Lr = Sheets("Clients").Range("A5").End(xlDown).Row + 1
Lr2 = Sheets("Clienttargetcollumn").Range("A6").End(xlDown).Row + 1
Ar1 = Application.Transpose(Sheets("Home").Range("C4:C7"))

Ar2 = Array(Ar1(1), Ar1(3), "", "", "", Ar1(4), "", "", "", Ar1(2))
Sheets("Clients").Range("A" & Lr & ":D" & Lr) = Ar1
Sheets("Clienttargetcollumn").Range("A" & Lr).Resize(, UBound(Ar2)) = Ar2
End Sub