Copy Range of Data to another Workbook

[ststern45]

Hi everyone,

I have 10 Excel workbooks in a folder C:\States\------.xls

I have 1 Excel workbook named Charts.xls in the same folder.
The worksheet tab is named Workouts

I would like to know if it is possible to:

1. Create a macro that will open a Dialog Box where I can enter the name any 1 of the 10 named workbooks in c:\States folder.

2. Copy and paste a range of cells B6:D100 into the Excel workbook Charts.xls (tab Workouts) cell range B6:D100.



Thanks,

Steve

 

[Lavan]

Steve, Try below code.. Its pastes the data on new workbook from Cell A1

Sub paste_to_workbook()

Dim spath As String
spath = "c:\states\"
Sheets("workouts").Activate
Range("B6:D100").Select
Selection.Copy
workbook_name = InputBox("Enter the workbook name to paste data")
Workbooks.Open Filename:=(spath & workbook_name)
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub

Hope it helps

Lavan