How to copy data from one workbook to a specific worksheet of another workbook

[VidyaVallaba]

Hi all,

I have tried a lot and could not achieve what i want. Need some help with this.

I have a sheet named sheet9 and i want to copy the data from this workbook-sheet9 to a workbook called importdata and sheet named "database".

Sub fin()
 Application.DisplayAlerts = False
 Dim fso     As Object
 Dim fldr    As Object
 Dim fil     As Object

 Dim wb As Workbook
 Dim wc As Workbook
 Dim wcs As Range
     strFldrPath = TextBox1.Value '"D:\ConsolidatNew" 'folder path

 Set wc = ActiveWorkbook
 Set wcs = wc.Worksheets("sheet2").Range("A1:P1")
 'wcs.Copy
 Set fso = CreateObject("scripting.filesystemobject")
 Set fldr = fso.GetFolder(strFldrPath)

 For Each fil In fldr.Files
 Set wb = Workbooks.Open(Filename:=fil.Path)
  
  
 Sheets.Add.Name = "sheet9"
 wcs.Copy

 wb.Sheets("sheet9").Range("a1").PasteSpecial

'calling a function to prepare sheet9 in opened file
     cols
' Workbooks("Import_to_DB_2014.19.2.xlsm").Activate
'trying to insert a code here which copy pastes data from sheet9 to sheet database (finding next empty row)
'wb.Close True
Next
End Sub

'macro to prepare sheet9
Sub cols()
    Dim i As Integer
    Dim ar As Variant
    Dim sh As Worksheet
    Dim lr As Long
    Dim rng As Range
     
    Set sh = Sheets("Sheet8")
    ar = sh.Range("A1", sh.Range("IV1").End(xlToLeft))
    lr = sh.Range("A" & Rows.Count).End(xlUp).Row
     
    For i = 1 To sh.Range("IV1").End(xlToLeft).Column
        Set rng = Sheets("Sheet9").Rows("1:1").Find(ar(1, i), LookAt:=xlWhole)
        If Not rng Is Nothing Then 'found
            sh.Range(sh.Cells(1, i), sh.Cells(lr, i)).Copy rng
        End If
    Next i
End Sub

The above code prepares a sheet9 which i want. Now i need to copy this sheet9 to thisworkbook(workbook which contains macro) sheet database

Can any one help me?

[JOHN H. DAVIS]

Maybe:

Sub VidyaVallaba()
ActiveWorkbook.Sheets("Sheet9").UsedRange.Copy Workbooks("importdata.xls").Sheets("database").Range("A1")
End Sub

[VidyaVallaba]

Thanks for replying but it gives over writes when i perform the above for more than 1 file!!

How can i find the next empty row a paste the values?Please help me i have been working on this since many days cant find a way out

[JOHN H. DAVIS]

You would have to change your references to reflect the variable you're using.

ie.

wb.Sheets("sheet9").UsedRange.Copy

In your code I don't see a workbook or sheetname for the destination.

Workbooks("importdata.xls").Sheets("database")

Note: I used file extension ".xls" you may need ".xlsm" or ".xlsx"

[JOHN H. DAVIS]

Thanks for replying but it gives over writes when i perform the above for more than 1 file!!

How can i find the next empty row a paste the values?Please help me i have been working on this since many days cant find a way out

For your destination:

Change this:

Workbooks("importdata.xls").Sheets("database").Range("A1")

to this

Workbooks("importdata.xls").Sheets("database").Range("A" & rows.count).End(3)(2)

[VidyaVallaba]

I am using this

ActiveWorkbook.Sheets("Sheet9").Range("A2", Range("A2").End(xlToRight).End(xlDown)).Copy
 Workbooks("Import_to_DB_2014.19.2.xlsm").Sheets("DB").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)

problem with this is if only one column is matched then it gives error while copying.
Error is the destination does not match the copied range.