Skip to main content

Excel Macro – Copy Sheets to Another File

Can use this Macro to copy data in multiple sheets into a unified single sheet in another excel file.

Open the source file. Open a blank workbook and save it with some name eg: ‘target’. Keep both source and target files open.

In the macro, replace ‘source’ with name of source file. Replace ‘target’ with name of the blank file.

Sub CopyLoop()

        Dim Ws As Worksheet
        Dim i As Integer
        Dim K As Integer
        i = 2  ‘ first row of copyselection

        ' Loop through all of the worksheets in the active workbook.
        Windows("source").Activate
        For Each Ws In Worksheets
        K = K + 1
         Ws .Range("A2:AL116").Copy
        Windows("target").Activate
        Cells(i, 2).Select
        ActiveSheet.Paste
        Range(Cells(i, 1),Cells(i+116,1)) = Ws .Name
        Windows("source").Activate
        i = i + 116  ‘last row of copy selection


      '  If K > 5 Then Exit For  ‘ uncomment this line if you want to give it a trial of 5 rows to see if everything is as expected

       Next

End Sub

Comments

Post a Comment

Popular posts from this blog

Siemens Washing Machine : Kill the Buzzer

The washing machine we have has the habit of sending out an irritating beep after it finishes the wash cycle. Ok enough; but these intermittent beeps go on and on and on till it is sure everyone came back home after your attending your funeral. One awful engineer who programmed the chip. Anyway, been looking around the net if there is a work around. Found this written by some Russian. I didn’t have patience to correct the grammar entirely. If the below does  not work, there is always the option of an Axe and ‘hey Siemens,..Heeeere is Johnyyy!) -- text You can change the volume of the buzzer according to your requirement. The operation procedure: 1. Switch on the machine,turn the program selector to Off . 2. Turn the program selector to cold Easy-care , press the additional function button Intensive stains and dont let go. You can hear the volume of the buzzer from minimum to maximum to off cycled (I didnt hear this!). If you decide the volume that yo...
2 comments
Read more

BitArray 0 and 1s to Hex and back

This code will let you convert an arbitrary string with 0s and 1s only into a Hex value, and back. Why? Maybe you have a Javascript component that renders based on a long array of bits. Lets say var b = "101010101100101001000101011101010010011100000010100110000010001010010011000000000001000010101001110000000001100010100000" The string can be stored in the DB as nvarchar(max) field, but then if you want to reduce the length by 1/8th, you can convert it into a Hexadecimal representation The above would be equivalent to 27029822 930010A9C018A0 Ok, this is the 'bit packing' concept implemented in a very crude way. Anyhow, the output is human readable and probably easier to pass in a JSON The functions are as below     Private Function bitArrayStrtoHex(b As String) As String         Dim sb As New System.Text.StringBuilder         For i As Integer = 0 To b.Length - 1 Step 8             Dim cu...
Post a Comment
Read more

HTML Sidebar menu without jQuery

Most examples I found on the net for a responsive sidebar had a reference to jQuery. For two lines of code, I find it an unnecessary overhead. So this is a basic page with no dependencies that has a toggle-able sidebar as well as a media query javascript that hides the sidebar below a breakpoint screen width. Modify as necessary. Maybe tricks could be added such as the one to change the hamburger to X when the sidebar is visible. https://jsbin.com/romiqov/2/edit?html,output <!DOCTYPE html> <html lang="en"> <head>     <meta charset="UTF-8">     <title>SideBar test</title>     <style>         * {             margin: 0;             padding: 0;             font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;         }         #sidebar { ...
Post a Comment
Read more