Code for Copying and Pasting data to a separate Workbook

[Dave K]

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

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.

 

[Per Jessen]

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("C120").PasteSpecial Paste:=xlPasteValues
End Sub

Regards,

Per

 

[Don Wiss]

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("C120").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("C120").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

Don <www.donwiss.com> (e-mail link at home page bottom).