active worksheet

[marys]

I am copying code that someone else wrote for another file
that accomplishes what I would like to do which is:
Copying a range from one workbook with one worksheet to
another workbook that has multiple worksheets with
different names but the pasting should be done on the
worksheet that is active.
The code I'm copying pasted the data to the same worksheet
each time so it refers to the name of the worksheet but I
need it to refer to the "active" worksheet whatever it is
named.
Here is the code line that needs to be changed:
Application.Goto Reference:=Targetbook.Worksheets
("nameofsheet").Range("C1")

Also question on the range reference ("C1") what is that
referring to exactly?
Thanks for help.

 

[Ron de Bruin]

Hi marys

If I understand you correct

If the workbook is open?
then this will copy the range a1:c10 from sheet1 in test.xls in the activesheet

Sub test()
Dim rng As Range
With Workbooks("Test1.xls").Worksheets("Sheet1")
Set rng = .Range("A1:c10")
End With
rng.copy Destination:=ActiveSheet.Range("C1")
End Sub

 

[marys]

Thanks very much, that got it going again but now has
stopped at another line:
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
with the error message: PasteSpecial method of Range class
failed
What next?

 

[Ron de Bruin]

Why use PasteSpecial if you copy all?

Example for pastespecial as values

Sub test()
Dim rng As Range
With Workbooks("Test1.xls").Worksheets("Sheet1")
Set rng = .Range("A1:c10")
End With
rng.Copy
ActiveSheet.Range("C1").PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
End Sub