Excel vba macro to copy cells and paste in another

Question

I apologize upfront...

I am an avid Excel user, but not skilled with VBA. Any help is appreciated.

1. I need the user to input a date in ddmmyyyy format.
2. Open a workbook based on that ddmmyyyy information (concatenate it with a file name).
3. Copy cells from the specific range in that workbook (AN1:AS1).
4. Paste the copied cells into another workbook. It should be pasted next to the date entered by the user.

Here is the code I tried so far:

dim input as string
input = inputbox("Insert date in format ddmmyyyy")
Workbooks.open (""\\vmp-avayaacc\reports\servicedesk\Josh\Agent By Skillset Performance" & input & "1155.csv"")
Workbooks("(""\\vmp-avayaacc\reports\servicedesk\Josh\Agent By Skillset Performance" & input & "1155.csv"")).Worksheets("Sheet1").Range("AN1:AS1").Copy _
    Workbooks("http://sharepoint.coh.org/SiteDirectory/ITS/BO/Dashboard.xls").Worksheets("Sheet1").Range("A1")

Can somebody help me make this work? Thank you.

Answer 1

You can change the last line to Range("A2"):

Workbooks(""\\vmp-avayaacc\reports\servicedesk\Josh\Agent By Skillset Performance" & input & "1155.csv"").Worksheets("Sheet1").Range("AN1:AS1").Copy _
Workbooks("http://sharepoint.coh.org/SiteDirectory/ITS/BO/Dashboard.xls").Worksheets("Sheet1").Range("A2")

And then add the date in front of it to cell A1:

Workbooks("http://sharepoint.coh.org/SiteDirectory/ITS/BO/Dashboard.xls").Worksheets("Sheet1").Range("A1").Value=input

Hope this helps.

Answer 2

Copy this code into the VBA window of a macro-enabled worksheet and either F5 execute or assign it as a macro to a button or other control object:

  Public Sub CopyCells()
      Dim wkb         As Excel.Workbook
      Dim wks         As Excel.Worksheet
      Dim wkb2        As Excel.Workbook
      Dim wks2        As Excel.Worksheet
      Dim strMSG      As String

      strMSG = InputBox("Insert date in format ddmmyyyy")

      Set wkb = Excel.Workbooks.Open("\\vmp-avayaacc\reports\servicedesk\Josh\Agent By Skillset Performance" & strMSG & "1155.csv")

      Set wkb2 = Excel.Workbooks.Open("http://sharepoint.coh.org/SiteDirectory/ITS/BO/Dashboard.xls")

      Set wks = wkb.Worksheets("Sheet1"): wks.Activate

      wks.Range("AN1:AS1").Copy

      Set wks2 = wkb2.Worksheets("Sheet1")

      wks2.Range("A1").PasteSpecial xlPasteAll

      Set wks = Nothing: Set wkb = Nothing

      set wks2 = nothing: set wkb2 = nothing
  End Sub