CODEWITHSUNDEEP
vbaexcel
Cris Reis
Cris Reis

Reputation: 179

vba excel 2010 copy data to another empty column sheet

Could everyone help me? Data is always is column A, sheet 1 and I want to copy and paste to a empty colunm on sheet2. At first time data goes to colunm A sheet 2 and second time(other data that I put on sheet1 column A) to colunm B sheet 2 and so on. I found this code but put data just on column A, Sheet2.

    Sub Insert_Data()

Sheets("Tratamento").Range("A1").Copy Destination:=Sheets("Dados").Range("A1")
For i = 2 To Sheets("Tratamento").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Tratamento").Range("A" & i).Copy Destination:=Sheets("Dados").Range("A" & Sheets("Dados").Cells(Rows.Count, 1).End(xlUp).Row + 1)
Next i

End Sub

Upvotes: 2

Views: 4512

Answers (2)

user2140261
user2140261

Reputation: 7993

The following command should do the job:

Select Case Sheets("Dados").Range("A1") = "" 

Case True 'paste in col A if A1 is empty

    Sheets("Tratamento").Range("A:A").Copy Sheets("Dados").Range("A1") 

Case False ' paste to next col

    Sheets("Tratamento").Range("A:A").Copy Sheets("Dados").Range("XFD1").End(xlToLeft).Offset(0, 1)

What this is Doing is Starting at the end of the second sheet and selecting the Column to the right of the last column with a value. This is Assuming that you will not be skipping any columns and that you have no other data on the page.

Upvotes: 1

cardmagik
cardmagik

Reputation: 1698

How about just this?

Sub Insert_Data()

Sheets("Tratamento").Range("A1").Copy Destination:=Sheets("Dados").Range("A1")
For I = 2 To Sheets("Tratamento").Cells(Rows.Count, 1).End(xlUp).Row

'  Made change on this line
Sheets("Tratamento").Range("A" & I).Copy Destination:=Sheets("Dados").Range("A" & I)

Next I

End Sub

Or is this too simplified? Maybe I didn't understand your question?

Upvotes: 0

Related Questions