CODEWITHSUNDEEP
excelvba
Pierre Marwa
Pierre Marwa

Reputation: 1

Open, copy and paste data from one wookbook to another based on date range

I am working on a VBA project whose aim is to open, copy and paste data from one workbook in a folder to another workbook in a different folder. I also would like to select the reports to open, copy and paste using the date range.

I am new to coding and below is my code: It opens, copies and pastes data, however, 1. Not all data is copied. There are some blanks in the worksheet and the code does not copy/paste anything after the blank cells 2. I tried adding some code to select the workbooks using a date range but nothing was working. Any ideas or thoughts? Any help will be appreciated.

Sub P_file()

Dim EnterDate As String
Dim Path As String
Dim wb As Workbook
Dim rng As Range

EnterDate = InputBox("Enter File Date")
Path = "C:\cpark\monthfile_" & Format(EnterDate, "m_d_yyyy") & ".xlsx"


'Assign value to object variables
Set wb = ThisWorkbook
Set rng = Range("A2")

Workbooks.Open (Path)

Range("A2", Range("A2").End(xlDown).End(xlToRight)).Copy


wb.Activate

rng.PasteSpecial (xlPasteValues)

Upvotes: 0

Views: 70

Answers (1)

Patrick
Patrick

Reputation: 232

Try this code. Hopefully this solves both of your issues.

Sub Pick_Report()

    Dim EnterDate As String
    Dim Path As String
    Dim WB_Copy As Workbook
    Dim WB_Paste As Workbook
    Dim Rng_Paste As Range
    
    EnterDate = InputBox("Enter File Date")
    Path = "S:\Reports\Name of Report_" & Format(EnterDate, "m_d_yyyy") & ".xlsx"
    
    'Assign value to object variables
    Set WB_Paste = ThisWorkbook
    Set Rng_Paste = WB_Paste.Sheets("Sheet1").Range("A2")'Change "Sheet1" to whatever you need it to be
    
    Workbooks.Open (Path)
    
    Set WB_Copy = Workbooks("Name of Report_" & EnterDate)
    
    WB_Copy.Activate
    ActiveSheet.UsedRange.Copy
            
    Rng_Paste.PasteSpecial (xlPasteValues)

End Sub

Upvotes: 0

Related Questions