Macro detects data and paste into another spreadsheet

[osevero]

Hi guys!

I'll need a macro that detects all data have been recorded starting row C3 (see photo) and then goes to spreadsheet 2 (plan2) and select the first empty row starting in C3 (see photo) and paste the data. Does anyone know the code?

Plan1:
10691

[osevero]

Plan2:
10693


Cheers!

[mancubus]

hi.
try this.



Sub copy_to_another_sheet()

Worksheets("Plan1").Range("C2").CurrentRegion.Offset(1).Copy Worksheets("Plan2").Range("C" & Rows.Count).End(xlUp).Offset(1)

End Sub

[osevero]

Yeah that code works fine! :) Thanks!! But I'll need something more complex. See the xlsx: 10694 So it is: in Plan1 only want to copy the data from column C and column F and paste in Plan2 these data and add value in column B (ID) that it may be (1,2,3,4...) such as it is in xlsx (in red in Plan2). Is this possible?

[nilem]

Hi Osevero,
try this

Sub ertert()
Dim i&
With Sheets("Plan1").Range("C3").CurrentRegion
i = .Rows.Count - 1
With .Offset(1).Resize(i)
Union(.Columns(1), .Columns(4)).Copy Sheets("Plan2").Cells(Rows.Count, 4).End(xlUp)(2)
End With
End With
With Sheets("Plan2").Cells(Rows.Count, 3).End(xlUp)(2).Resize(i)
.FormulaR1C1 = "=ROW(RC[-2])-3"
.Value = .Value
End With
End Sub

[osevero]

nilem thanks! :bow: the code works fine! If I only want to copy data from certain columns how code would be? Sees this new xlsx I did to see what I'm talking about: 10695
Note this is not to copy all columns Plan1, only those listed in Plan2

Cheers!

[nilem]

try

Sub ertert()
Dim i&
With Sheets("Plan1").Range("B6").CurrentRegion
i = .Rows.Count - 1
With .Offset(1).Resize(i)
Union(.Columns(1).Resize(, 9), .Columns(12), .Columns(14).Resize(, 3)).Copy _
Sheets("Plan2").Cells(Rows.Count, 3).End(xlUp)(2)
End With
End With
With Sheets("Plan2").Cells(Rows.Count, 2).End(xlUp)(2).Resize(i)
.FormulaR1C1 = "=ROW(RC[-2])-6"
.Value = .Value
End With
End Sub

[osevero]

Almost there! There are some problems... I just wanted to copy the values ​​within the cell and not the cell format (borders etc). Another problem, the code copies and paste the names of the columns (Date, welder, tube ...) I was trying to solve but I can't... please see if you can do it: 10697

[nilem]

see attachment

[osevero]

Thanks nilem but you have a error in Plan2, after the Frequency column is missing the column Efficiency. See if you can fix the code please

[nilem]

Oops, change please this line

Union(.Columns(1).Resize(, 9), .Columns(12), .Columns(14).Resize(, 3)).Copy
with this

Union(.Columns(1).Resize(, 10), .Columns(12), .Columns(14).Resize(, 3)).Copy

[osevero]

Thanks nilem!! :bow:
The macro button has to stay on Plan1 and it does that when I click the macro is made that "flashback" to go to Plan2 and paste the data there back to Plan1, it's a shame the macro to do this...