VBA to copy from one worksheet to another

chally3

Board Regular
Joined
Mar 22, 2006
Messages
155
Hi everyone, Can anyone help with some VBA please.

I am wishing to copy data from a worksheet called "ALL OPEN WOS" (Columns A-J, ROW 2 onwards)
and paste into the worksheet "HISTORICAL DATA" with no formulas copied, and in the first blank row onwards.
This is something that will be run daily, so copying to the first blank row is essential.


Thank you kindly inadvance
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
How about
Code:
Sub Copysht()
Sheets("ALL OPEN WOS").UsedRange.Offset(1).Copy
Sheets("HISTORICAL DATA").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End Sub
 
Upvote 0
This will do it:
Code:
Sub test()

With Worksheets("ALL OPEN WOS")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 10))
End With
With Worksheets("HISTORICAL DATA")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(lastrow2, 1), Cells(lastrow2 + lastrow, 10)) = inarr
End With


End Sub
 
Upvote 0
Hiya, for some reason its copying over the data ok, but them replicating in in row 8643 onward's. (This is for code from Fluff)
The 2nd code from "offthelip" is not working, no errors are shown either.
 
Last edited:
Upvote 0
Whose code are you talking about, or is it both codes?
 
Upvote 0
Whose code are you talking about, or is it both codes?

Hi Fluff, this is for your code. its really odd as it copies over perfectly. But for some reason its duplicating further down the same spreadsheet, which what seems to be row 8643 onwards. Im just rebuilding the worksheet from scratch to ensure its nothing corrupt on the spreadsheet.
 
Last edited:
Upvote 0
All resolved! Seem the main spreadsheet I was copying from had duplicated data!

Apologies, your code is perfect! Thank you :)
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
There were a couple of errors in my code too: this should work better:
Code:
ub test()

With Worksheets("ALL OPEN WOS")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(lastrow, 10))
End With
With Worksheets("HISTORICAL DATA")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
Range(.Cells(lastrow2, 1), .Cells(lastrow2 + lastrow - 1, 10)) = inarr
End With




End Sub
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top