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.

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

"Dave K" <(E-Mail Removed)> skrev i en meddelelse
news:63238dc4-61fe-4635-a371-(E-Mail Removed)...
> 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.

On Sat, 8 Mar 2008 22:33:11 +0100, Per Jessen <(E-Mail Removed)> 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("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).