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

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