Hey,
i have a big 130,000 row data base which i pull on daily bases.
i have created a script which lets you :
1.pick the file you want
2.copies it using .CurrentRegion funciton
3.pastes it to a new sheet in my workbook.
i want to add that new file as a new sheet to my current workbook, when i copy and paste it it comes acorss some empty rows in the data base which does not allow me to copy the whole SHEET i just imported..
i have creatd another script which goes trough the file and deltes all empty rows so it will be continuous and then i will be able to copy the whole data.
but i dont know where to place the CALL command for it to delete the rows before it copies it.
(IMPORTS THE FILE)
'''''''''''''''''''''''''''''''''''''
Sub import()
Dim textfile As Workbook
Dim openfiles As Variant
Dim i As Integer
openfiles = Application.GetOpenFilename(Title:="Select file(s) to import", MultiSelect:=True)
call blank(where do i call the function for it to delete the rows before coping it .)
For i = 1 To Application.CountA(openfiles)
Set textfile = Workbooks.Open(openfiles(i))
Call blank
textfile.Sheets(1).Range("A1").CurrentRegion.Copy
Workbooks(1).Worksheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
textfile.Close
Application.ScreenUpdating = False
Next i
End Sub
'''''''''''''''''''''''
(DELETES EMPTY ROWS)
''''''''''''''''''''''''''''
Sub blank()
Dim x As Long
With ActiveSheet
For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(.Rows(x)) = 0 Then
ActiveSheet.Rows(x).Delete
End If
Next
End With
End Sub
i have a big 130,000 row data base which i pull on daily bases.
i have created a script which lets you :
1.pick the file you want
2.copies it using .CurrentRegion funciton
3.pastes it to a new sheet in my workbook.
i want to add that new file as a new sheet to my current workbook, when i copy and paste it it comes acorss some empty rows in the data base which does not allow me to copy the whole SHEET i just imported..
i have creatd another script which goes trough the file and deltes all empty rows so it will be continuous and then i will be able to copy the whole data.
but i dont know where to place the CALL command for it to delete the rows before it copies it.
(IMPORTS THE FILE)
'''''''''''''''''''''''''''''''''''''
Sub import()
Dim textfile As Workbook
Dim openfiles As Variant
Dim i As Integer
openfiles = Application.GetOpenFilename(Title:="Select file(s) to import", MultiSelect:=True)
call blank(where do i call the function for it to delete the rows before coping it .)
For i = 1 To Application.CountA(openfiles)
Set textfile = Workbooks.Open(openfiles(i))
Call blank
textfile.Sheets(1).Range("A1").CurrentRegion.Copy
Workbooks(1).Worksheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
textfile.Close
Application.ScreenUpdating = False
Next i
End Sub
'''''''''''''''''''''''
(DELETES EMPTY ROWS)
''''''''''''''''''''''''''''
Sub blank()
Dim x As Long
With ActiveSheet
For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(.Rows(x)) = 0 Then
ActiveSheet.Rows(x).Delete
End If
Next
End With
End Sub