Code for Copying and Pasting data to a separate Workbook
Hello -
I have a workbook that I copy from on a daily basis and paste into another workbook. In each instance, the workbook that I paste to has a slightly different name (i.e., to account for the change in date). As an example: I copy from Sheet5, Range A1:B20 from Workbook A, and paste it into Sheet 3 of Workbook B, Range C1:D20. Is there a way to automate this via VBA? For example, is there code that can say: select this range and place it, via paste special values, in this workbook (perhaps where a dialog box prompts the user for the workbook name) in this range? Thanks in advance for any suggestions or sample code you may be able to provide. |
Code for Copying and Pasting data to a separate Workbook
Hi
Place this code in the workbook that you are copying data from. I assume that the destination workbook is opened before you run this code. Sub CopyData() Dim CopyTo As String CopyTo = InputBox("Enter name of workbook to paste data to", "Regards, Per Jessen") Workbooks("Book1").Activate ' Change to suit Sheets("Sheet5").Range("A1:B20").Copy Workbooks(CopyTo).Activate Sheets("Sheet3").Range("C1:D20").PasteSpecial Paste:=xlPasteValues End Sub Regards, Per "Dave K" skrev i en meddelelse ... Hello - I have a workbook that I copy from on a daily basis and paste into another workbook. In each instance, the workbook that I paste to has a slightly different name (i.e., to account for the change in date). As an example: I copy from Sheet5, Range A1:B20 from Workbook A, and paste it into Sheet 3 of Workbook B, Range C1:D20. Is there a way to automate this via VBA? For example, is there code that can say: select this range and place it, via paste special values, in this workbook (perhaps where a dialog box prompts the user for the workbook name) in this range? Thanks in advance for any suggestions or sample code you may be able to provide. |
Code for Copying and Pasting data to a separate Workbook
On Sat, 8 Mar 2008 22:33:11 +0100, Per Jessen wrote:
Place this code in the workbook that you are copying data from. I assume that the destination workbook is opened before you run this code. Sub CopyData() Dim CopyTo As String CopyTo = InputBox("Enter name of workbook to paste data to", "Regards, Per Jessen") Workbooks("Book1").Activate ' Change to suit Sheets("Sheet5").Range("A1:B20").Copy Workbooks(CopyTo).Activate Sheets("Sheet3").Range("C1:D20").PasteSpecial Paste:=xlPasteValues End Sub Activating a workbook slows down the macro. Faster is: Sub CopyData() Dim CopyTo As String CopyTo = InputBox("Enter name of workbook to paste data to", "Regards, Per Jessen") If CopyTo = "" Then Exit Sub Application.ScreenUpdating = False Workbooks("Book1").Sheets("Sheet5").Range("A1:B20" ).Copy ' Change to suit Workbooks(CopyTo).Sheets("Sheet3").Range("C1:D20") .PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End Sub Don <www.donwiss.com (e-mail link at home page bottom). |
All times are GMT +1. The time now is 09:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com