Copy data from one workbook that is on specified location and paste it to another workbook

[aman2059]

Hi,

I am trying to copy data from one workbook that is on specific location and then paste data to the another workbook and then save the 'another' workbook with specific name.

I am able to save workbook but I am unable to copy data from one workbook to another. code gives an error. Below is my code. Please help.

Sub Save214012016()
 
    Dim FName As String
    Dim FPath As String
    Dim NewBook As Workbook
    Dim wb2 As Workbook
    
 
    FPath = "C:\Users\aman.bhardwaj\Desktop\Ideas\Learning process\Master files\testing\saved report"
    FName = "LearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx"
 
    Set wb2 = Workbooks.Open("C:\Users\aman.bhardwaj\Desktop\Ideas\Learning process\30th November 2015\raw\LearnersinMandateDataExport_3657.xls")
    ThisWorkbook.Activate
    Set NewBook = Workbooks.Add
    Windows("LearnersinMandateDataExport_3657.xls").Activate
    Sheets("Sheet1").Select
    wb2.Sheets("Sheet1").Copy Before:=NewBook.Sheets(1)
 
    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "File " & FPath & "\" & FName & " already exists"
    Else
        NewBook.SaveAs Filename:=FPath & "\" & FName
    End If
    
     
End Sub

 

[aman2059]

This is resolved. Below is the code

Sub Save6()
    
    Dim FName As String
    Dim FPath As String
    Dim NewBook As Workbook
    Dim wb2 As Workbook
    
    FPath = "C:\Users\aman.bhardwaj\Desktop\Ideas\Learning process\Master files\testing\saved report"
    FName = "LearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx"
    
    Set wb2 = Workbooks.Open("C:\Users\aman.bhardwaj\Desktop\Ideas\Learning process\30th November 2015\raw\LearnersinMandateDataExport_3657.xls")
    wb2.Sheets("Sheet1").Copy    'Creates a new workbook with the copied sheet
    Set NewBook = ActiveWorkbook
    
    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "File " & FPath & "\" & FName & " already exists"
    Else
        NewBook.SaveAs Filename:=FPath & "\" & FName
    End If
         
End Sub

 

[aman2059]

Bumping to this thread.

I am able to copy all the data in new workbook with above code. However, the problem is that in the old workbook, data has few filters in it. So when it copies data to new workbook, it also copies filters. Could anyone please suggest me the edit to above code to only copy visible cell to the new workbook..

Thank you in advance.

 

[aman2059]

Can anyone please help me in the above?