Copy contents to a selected sheet in another workbook

[Razorboy]

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]

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.)

 

[royUK]

I think this might be what you want


Code:
--------------------
Dim bk As Workbook
Dim sht As String
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

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


--
royUK

Hope that helps.

RoyUK