Copy contents to a selected sheet in another workbook
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 |
Copy contents to a selected sheet in another workbook
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 |
Copy contents to a selected sheet in another workbook
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 |
All times are GMT +1. The time now is 03:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com