• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Paste data from one work sheet to other in diffrent way.

sanket.katkar

New Member
Actually i am trying out some thing challenging using macros.

There two workbook lets say Workbook1 and workbook2.

From workbook 1 i have to copy data from D5 to D52. and paste that data in workbook 2.

But the process should be ;

When i click on the button in Workbook2 it should ask me the source workbook from where the data

has to be copied and after selecting that work book it should ask from which line it should

paste the data in the sheet. Paste should be special value.

Thanks in advance....
 
Will need a little bit of work to make sure the sheet names are correct, but this should be the basic structure:

[pre]
Code:
Sub OpenAndCopy()
Dim MyBook As String
Dim MyRow As Integer
Dim SourceBook As Workbook

MyBook = Application.GetOpenFilename
MyRow = InputBox("Which row should we paste to?", "Destination", 1)

'In case the workbook is already open, ignore errors
On Error Resume Next
Workbooks.Open MyBook
On Error GoTo 0
Set SourceBook = ActiveWorkbook

'What worksheet will we be copying from?
SourceBook.Worksheets("Sheet1").Range("D5:D52").Copy

ThisWorkbook.Activate
'I am assuming that the active sheet is where you want to paste the data
ActiveSheet.Cells(MyRow, "A").PasteSpecial xlPasteValues

'Should we close the other workbook?
SourceBook.Close savechanges:=False

End Sub
[/pre]
 
Hi Luke,


Maybe following approach can be used to get the desired data based on selection:

[pre]
Code:
Dim SelRng As Range
Dim lngRow As Long
Dim strSht As String, strWbk As String

On Error Resume Next
Set SelRng = Application.InputBox("Which row should we paste to?", "Destination", Type:=8)
If Not SelRng Is Nothing Then
lngRow = SelRng.Row                 'Row Desired
strSht = SelRng.Parent.Name         'Sheet Name Desired
strWbk = SelRng.Parent.Parent.Name  'Workbook Desired
Else
MsgBox "Invalid Range!", vbInformation: Exit Sub
End If
[/pre]

Rgds,
 
Back
Top