Dim myName as string
myName = someworkbook.worksheets("somesheetname").range("N1 ").value

Then you could use:
bk.Worksheets(myName).Cells(lRow, 1).PasteSpecial xlPasteValues

(If I understood the question.)


Razorboy wrote:

Hello everyone,

i have created a macro that will copy and paste values and formats
from one sheet into "Sheet1" in another workbook. However i need to
copy the data into the sheet of the same name. Is this possible? The
sheet name in the orininal is currently variable based on a list and
is linked to cell N1. How do i modify the macro below to acheive
this?

Sub Test()
Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long

On Error Resume Next
Set bk = Workbooks("Flat Files.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C\Flat Files.xls")
End If

lRow = bk.Worksheets("Sheet1").Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ThisWorkbook.ActiveSheet.Range("CopyRange").Copy
bk.Worksheets("Sheet1").Cells(lRow, 1).PasteSpecial xlPasteValues
bk.Worksheets("Sheet1").Cells(lRow, 1).PasteSpecial xlPasteFormats
bk.Worksheets("Sheet1").Cells(lRow, 1).PasteSpecial
xlPasteColumnWidths

--

Dave Peterson