Sunday, February 28, 2016

Scrolling By Cutting Rows With Spin Buttons

Cut And Insert Row To Down/Up With VBA

             Selected row is cut and it is scrolled with spin buttons to down/up.
UserForm is opened as automatically when workbook is opened. Spin buttons can be used on all pages of the workbook.


Thursday, February 25, 2016

Macro To Highlight Duplicate Records

             This Excel Vba Code is to find duplicate values in sheets of workbook. The duplicate records are searched in the used ranges of all sheets by macro :

For syf = 1 To Sheets.Count
Sheets(syf).Select
ActiveSheet.UsedRange.Select
...

Background of found records are painted and new borders are drew  :

For Each mycell In Selection
    If WorksheetFunction.CountIf(Selection, mycell.Value) > 1 Then
        mycell.Interior.ColorIndex = 8
        mycell.Borders.Weight = xlThin
        mycell.Borders.ColorIndex = 44
    End If
Next


If desired  the processes can be recovered with "Undo Button".

Friday, February 19, 2016

Listing The Filtered Data On Separate WorkBooks

Creating New Books According To The Unique Values That Consists Of Filtering Result

          UserForm opens automatically when the workbook is opened.When pressed the button on the userform, unique values are listed with the filtering method in a new sheet. Named folder according to date is created.

           According to the unique values in the column A , new workbooks are created in folder , the data are copied into this workbooks.The hyperlinks are created for this new workbooks.


Friday, February 12, 2016

Transferring Data To The Text File

          In this example, the data are transferred into text file as separated by spaces. In the same folder with the Excel book, "records" named text file is created.

The data are transferred to this text file starting from the "column B".




Friday, February 5, 2016

Excel Vba Get Data From Closed Workbook

Excel Vba Get Data From Closed Workbook
Import Data From Closed Book Based On The Value In The Cell (Without Using ADO)

         The wanted values are being fetched from other closed books according to Product No. in the in main file's column B . The fetched values are listed in sequential rows.


          There are 3 books in the same folder -according to need may be more than files - : main_file.xls , 13,xls , 14.xls . In our example, product numbers are starting 13 or 14 . For this reason ,the files are opens according to product no. (13.xls or 14.xls) and values are entered into main_file.xls.

Wednesday, February 3, 2016

Copying The Chosen Numeric Values To The Other Page

Useful Macros - 12
Copying The Chosen Numeric Values To The Other Page

           In the previous examples, the columns that contain non-numeric values were copied .

We will copy the numerical values that comply with criteria in this template . 

There are numeric values in Column I.Now let's copy great values from 20.000 in Column I to other pages.
Using code as follows :

"For i = 1 To a
If Val(Cells(i, 9)) > 20.000 Then
Sheets("Data").Rows(i).Copy sh2.Rows(last)
last = last + 1
End If
Next i "


Tuesday, February 2, 2016

Copying The Values To The Other Page

Useful Macros – 11
Copying The Chosen Values To The Other Page

            This template is similar to the example in previous tutorial (Useful Macros-10).
Difference between two example; chosen data  are just copied (data aren't cut) in this template.

Also in this template , font color of the data in the Column C was selected red :
"For k = 2 To ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
       sh2.Cells(k, 3).Font.ColorIndex = 3
Next "