Macro to copy cells from different worksheet into one

  • Hi all,

    I have several spreadsheets that have data that scattered all over the worksheet. I need a macro that can look into each cell in each worksheet, copy the value if there's any and paste it into a new worksheet under selected columns.

    I am stuck with trying to create loops that can look into each cell and copy the value.

    Can any kind soul offer any help?


  • Hi mic

    Welcome to the board.

    This code will copy all cells from all sheets, except one called "CollectAll". The sheet CollectAll is the sheet used to paste all data to as Values only.

  • Hi Dave and joelsmally,

    Thank you very much for your reply. I appreciate it very much.

    Maybe my question does not reflect my real problem.

    I have attached 2 files for your guidance. The file "stock compile" copies and pastes the information from "stock". As you can see, the horizontal information in "stock" is right now in the vertical situation, and the delivery date is duplicated according to the number of stock we have in a row. For example, in "stock" on 01-05, we have 3 cargoes, namely DD,EE & GG. These information is inserted row by row and thus duplication of the date is required.

    The location and customer information is also being searched in each row and inserted in the specified columns in "stock compile".

    One good thing is that the template does not change and the rows and column's location can be fixed.

    I am thinking of using loops that changes the row numbers and column numbers.

    Is it possible to do that?

    Thank you very much again for your help.

  • Hi Joelsmally,

    Yup, I create the 'stock compile' manually from stock. Reason is that I do not know how to create loop to check the first row in a range before going to the second row.

    The codes below only check for the first cell that has number on it but it does not proceed further.

    Sub GetBlankCell()
    Dim myRange As Range, cell As Range
    Set myRange = ActiveSheet.Range("C121:C141")
    For Each cell In myRange
    If cell > "0" Then
    MsgBox cell.Value
    Range("c4") = cell
    Exit For
    End If
    Next cell
    End Sub

    How can I improve this code further so that it will check all the cells within the range of (C121:C141), which is in row 8, copy each individual cell into another workbook under selected columns and proceed the next range in row 9?

    Sorry for the trouble but I believe this is the one that I have problem with.

    Thanks! :saint:

  • Hi Joelsmally,

    I think the draft below may be able to provide a clearer picture of my problem.

    Below is the English version code that may make my question clearer:

    Open file AA
    Search from column J8 to Z8
    If found a value in J8
    copy the cell
    open a new workbook and name it BB
    go to column C4 in BB to paste the value
    go back to AA and copy J6
    go to BB and paste into A4
    go to AA and copy G8
    go to BB and paste in B4
    go to AA and copy F8
    go to BB and paste in E4
    go to AA and copy D8
    go to BB and paste in L4
    go to the next cell (K8)

    If found a value in k8
    copy the cell
    avtivate BB
    go to column C5 in BB to paste the value
    go back to AA and copy k6
    go to BB and paste into A5
    go to AA and copy G8
    go to BB and paste in B5
    go to AA and copy F8
    go to BB and paste in E5
    go to AA and copy D8
    go to BB and paste in L5

    repeat the same process until Z8
    Go to the next row in AA
    repeat the searching and pasting from J9 to Z9
    Save BB
    Close AA

    As you can see, I need to copy from BB horizontally and paste in AA vertically. If I can find 5 values in BB, AA will require 5 rows to reflect each value.

    Thanks very much for your help!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!