Button to import and sort data

[joharmenezes]

I have 2 workbooks, book1 http://www.filedropper.com/book1_4 and book2 http://www.filedropper.com/book2_2. I have a button in book1 which when clicked should import data from Column B of book2 to Column A in Sheet2 of book1 and should also sort the column in ascending order. Book1 and Book2 can be in the same or different folders. I would like to also know if this can be done with macros alone or is VBA also required?


Thanks

 

[sgmpatnaik]

@T100


Hi


i have not that much of idea but i create one code for you please Copy the below Code and paste in the created button


Note: it will copy from the Book2 in to Mastersheet of the Original Book you need to change the Sheet name to Mastersheet of your original book1 and save the file with 97 to 2003 or .xlsm file format


Option Explicit

Sub Button1_Click()


Dim wsMaster As Worksheet

Set wsMaster = Worksheets("Mastersheet")

Dim nextrow As Long


Dim Files(1 To 1) As String

Files(1) = "Book2.xlsx"


Dim filepath As String

filepath = "C:UsersDesktopSingle" 'change the file path as per your required

Dim filenum As Integer


Application.ScreenUpdating = False


With wsMaster

.Range("A2:A65536").Clear

End With


For filenum = 1 To 1

nextrow = wsMaster.UsedRange.Rows.Count + wsMaster.UsedRange.Row


Workbooks.Open (filepath & Files(filenum))

Worksheets("Sheet1").Range("A2:A1000").Copy _

wsMaster.Cells(nextrow, 1)

Next


ThisWorkbook.Activate

Call CloseAll


Application.ScreenUpdating = True


Range("A:A").Select

ActiveWorkbook.Worksheets("Mastersheet").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Mastersheet").Sort.SortFields.Add Key:=Range( _

"A2:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets("Mastersheet").Sort

.SetRange Range("A2:A1000")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("A1").Select


End Sub


Sub CloseAll()

' Close all but the active workbook


Dim wkbk As Workbook


Application.ScreenUpdating = False


For Each wkbk In Application.Workbooks

If wkbk.Name <> ActiveWorkbook.Name Then

wkbk.Close SaveChanges:=False 'or make it true

End If

Next


Application.ScreenUpdating = True


End Sub


Hope it will solve your problem other wise inform us


Thanks


SP

 

[SirJB7]

Hi, T100!


I think there's an underlying wrong concept. Macros are made with VBA instructions, so up to a certain extent macros and VBA are the same thing. At ancient times, there were another type of macros (Excel 4.0 macros) that were written in a kind of worksheet, but I think you're not talking about them.


Regarding the execution mode you may find these modes:

a) manually run macros, from Alt-F8

b) manually run macros, clicking on objects -either form or ActiveX controls- (command buttons, etc.)

c) manually run macros, clicking on shapes (macros assigned to them)

d) automatically run macros, triggered by events -except b)- (change cell values, deactivate worksheets, open workbooks, etc.)


Hope it helps.


Regards!