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.

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.

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