View Full Version : Macro detects data and paste into another spreadsheet
osevero
10-11-2013, 06:28 PM
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
10-11-2013, 06:31 PM
Plan2:
10693
Cheers!
mancubus
10-12-2013, 01:31 AM
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
10-12-2013, 03:21 AM
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
10-12-2013, 07:30 AM
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
10-12-2013, 11:49 PM
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
10-13-2013, 02:29 AM
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
10-13-2013, 04:04 AM
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
10-13-2013, 04:53 AM
see attachment
osevero
10-13-2013, 05:49 AM
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
10-13-2013, 05:54 AM
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
10-13-2013, 09:43 AM
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.