Hi Steve
T
he worksheet not always being the same is no problem you just run the
code from the sheet your on, the start sheet. The destination sheet
for your paste must have some sort of rule, there must be some way to
identify which sheet you are about to paste to.

For example you may have a unique word on the destination sheet that
you can look for prior to pasting. Can you give some more information
on this please. There should also be some sort of rule for the range
size to copy. Is it the used range on the start sheet? Does it
always start in a given point like A1. Anyways if you could provide
more information you will get a better answer. In the mean time here
is an answer with some smarts built in.

Ask any questions you wish if it does not help.

Take care

Marcus

'Run this from the sheet you wish to copy from.

Option Explicit
Sub MoreInfoPls()
Dim sh As Worksheet
Dim ws As Worksheet
Dim lw As Integer
Dim lwb As Integer
Dim lwc As Integer

Set sh = ActiveSheet
Set ws = Sheets("Sheet1")
lw = Range("A" & Rows.Count).End(xlUp).Row
lwb = ws.Range("B" & Rows.Count).End(xlUp).Row
Range("A1:B" & lw).Copy 'Copies to the last used row in col B
ws.Range("B" & lwb).PasteSpecial xlValues 'Pastes just the vals
lwc = ws.Range("C" & Rows.Count).End(xlUp).Row 'Last used row in C
ws.Range("B" & lwb & ":C" & lwc).Name = "Result"

End Sub