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?
Bookmarks