QUESTION: Hi Mr Karelse

many thanx for your help

I would appreciate further assistance since I did not make it clear for you in the first instance

My stock.xlsx exist in a folder called called current month (source)and for the new month folder (destination), the stock.xlsx is copied therein

In place of dates in your previous answer can it be changed to if the input answer is 30, the macro would copy the column P:P from
/current month/stock.xlsx/ws30 to destination /new month/H:H

and if the input is 31 it would the same but from ws 31

I really appreciate the time and effort you are placing in solving my problem for which I than you in anticipation

your previous soln
Sub CopyPasteLastMonth()
  Dim dDate As Date
  Dim sAnswer As String
  Dim sLastDay As String
  dDate = #1/1/1900#
  sAnswer = InputBox("Please enter the date", , Format(Date, "yyyy/mm/dd"))
  On Error Resume Next
  dDate = CDate(sAnswer)
  If dDate <> #1/1/1900# Then
      'Date entered; proceed
      sLastDay = Day(DateSerial(Year(dDate), Month(dDate) + 1, 0))
      With Worksheets(sLastDay)
        Intersect(.UsedRange.Offset(1), .Range("P:P")).Copy Destination:=Worksheets("1").Range("H2")
      End With
  End If
End Sub

ANSWER: Can you please be a bit more precise:

Which is the source *workbook* (is it the one containing the VBA code?)?

Is the target workbook not yet open in Excel?

What is the exact location of both source and target workbooks?

---------- FOLLOW-UP ----------

QUESTION: the source workbook  would be in folder new month and contains the file stock.xlsx and the destination folder would be new and the file stock.xlsx
both folders will be on the desktop

The vba code is contained in a separate sheet with all macros and is activated by a button

Would you like me to send you all the folders and the menu sheet



I have not tested this, but probably:

Sub CopyPasteLastMonth()
   Dim sAnswer As String
   Dim sLastDay As String
   sAnswer = InputBox("Please enter the month you need copied")
   If Len(sAnswer) > 0 Then
       'Month entered; proceed
       'Open the stock file which is in the New folder
       Workbooks.Open "PathToYourDesktop\New Month\Stock.xlsx"
       With Worksheets(sAnswer)
         Intersect(.UsedRange.Offset(1), .Range("P:P")).Copy Destination:=Worksheets("1").Range("H2")
       End With
       Workbooks.Open "PathToYourDesktop\New\Stock.xlsx"
   End If
End Sub

Note you have to change the code so PathToYourDesktop is correct.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.