Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Copying data from One Workbook And range valuing (Excel 2007)

    I need VBA Code that will copy data from workbook stock1 "sheet1" columns A and then copying in into the appropriate sheet (I need a mesage box to pop up asking which sheet number to copy to) into the destination workbook price Updates Prof Values by using paste Special Values. Each month the data will be copied into a new worksheet for eg Apr, May etc. When you select the sheet from the Pop up message for eg Apr, then the data from the source sheet must be copied into the sheet "apr" using Pastespecial values.

    I have written code, but cannot get it to work properly

    See Code Below

    Sub Copy_Data()

    Dim ws, chk, wkb1, BookA
    Sheets("Sheet1").Select
    Do
    BookA = "C:My DocumentsPrice Updates.xls"
    Workbooks.Open BookA
    ws = InputBox("Enter name of destination sheet.")
    wkb1 = ActiveWorkbook.Sheets("sheet1").Columns("A")
    ActiveWorkbook.Close False
    If ws <> "" Then
    On Error Resume Next
    chk = Sheets(ws).Name
    On Error GoTo 0
    If chk = "" Then
    MsgBox "No such sheet name."
    GoTo n
    Else
    ActiveWorkbook.Sheets(ws).Columns("A") = wkb1
    Exit Do
    End If
    Else: Exit Do
    End If
    n:
    Loop
    End Sub


    I have attached the destination file

    Your assistance in this regard will be most appreciated

    Howard
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying data from One Workbook And range valuing (Excel 2007)

    I'm not sure I understand, but try this:

    Sub Copy_Data()
    Dim wbkSrc As Workbook
    Dim wbkTrg As Workbook
    Dim wshSrc As Worksheet
    Dim wshTrg As Worksheet
    Dim strSheet As String

    On Error GoTo ErrHandler

    Set wbkTrg = ActiveWorkbook
    Set wbkSrc = Workbooks.Open("C:My DocumentsPrice Updates.xls")
    Set wshSrc = wbkSrc.Sheets("sheet1")
    strSheet = InputBox("Enter name of destination sheet.")
    Set wshTrg = wbkTrg.Worksheets(strSheet)
    wshSrc.Range("A").Copy
    wshTrg.Range("A1").PasteSpecial Paste:=xlPasteValues
    wbkSrc.Close SaveChanges:=False
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copying data from One Workbook And range valuing (Excel 2007)

    Hans

    Do you not have the Source and target mixed up?
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying data from One Workbook And range valuing (Excel 2007)

    I don't think so, but who knows? I wrote that I didn't quite understand the question.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copying data from One Workbook And range valuing (Excel 2007)

    In case Hans has misunderstood your requirement. you might want to try the following code.
    <pre>Sub Copy_Data()
    Dim oSht As Worksheet
    Dim ShtExists As Boolean
    Dim ws, BookA, Source
    Sheets("Sheet1").Select
    Source = ActiveWorkbook.Name

    BookA = "C:My DocumentsPrice Updates.xls"
    Workbooks.Open BookA
    ws = InputBox("Enter name of destination sheet.")

    For Each oSht In ActiveWorkbook.Sheets
    If oSht.Name = ws Then
    ShtExists = True
    Exit For
    End If
    Next oSht

    If Not ShtExists Then
    MsgBox "No such sheet name."
    Exit Sub
    End If

    Workbooks(Source).Sheets("Sheet1").Range("A").Copy

    ActiveWorkbook.Sheets(ws).Range("A1").PasteSpecial xlPasteValues

    Application.DisplayAlerts = False
    Workbooks(Source).Close False
    Application.DisplayAlerts = True

    End Sub</pre>

    Regards
    Don

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Copying data from One Workbook And range valui

    Hi Hans & Don

    Thanks for your efforts. Don's Code works Perfectly

    Hans when running your code, it comes up with Subscript out of range. Nevertheless thanks for your effort, which is allways appreciated

    Regards

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •