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

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
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=25053