Results 1 to 8 of 8

Thread: VBA to copy data from different tabs to new sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to copy data from different tabs to new sheet

    Hi,
    I have data in multiple sheets in active workbook.

    Each sheet has unique ID in cell H1 and each sheet has value in ‘column G’ and Date in ‘column A’.

    I want to copy paste the unique ID and value in ‘column G’ and yesterday’s date (excluding weekends) in new sheet.

    Thank you so much in advance

  2. #2
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from different tabs to new sheet

    create a sheet with name data_store and try this macro
    Code:
    
    
    Code:
    Option Explicit
    
    
    Sub test()
    Dim CheckA, CheckG, CheckH
    Dim g%, lr%, x, m%: m = 2
    Dim sh As Worksheet
    Sheets("data_store").Range("A2").CurrentRegion.ClearContents
     For Each sh In Sheets
        If sh.Name <> "data_store" Then
            CheckA = IsDate(sh.Cells(1, 1)) * 1
            CheckG = Not (IsEmpty(sh.Columns("G"))) * 1
            CheckH = Not (IsEmpty(sh.Cells(1, "H"))) * 1
           x = CheckA * CheckG * CheckH
               If x <> 0 Then
                  With Sheets("data_store")
                       g = sh.Cells(Rows.Count, "G").End(3).Row
                       .Cells(m, 1) = sh.Cells(1, 1)
                       .Cells(m, 2).Resize(g).Value = _
                          sh.Range("G1").Resize(g).Value
                       .Cells(m, 3) = sh.Cells(1, "H")
                     m = m + g
                 End With
              End If
         End If
     Next
    
    
    End Sub
    
    



    Last edited by salim hasan; Sep 20th, 2019 at 02:38 PM.

  3. #3
    Board Regular
    Join Date
    Oct 2018
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from different tabs to new sheet

    I ran the macro.. Its running but not giving any result nor giving any error message.

  4. #4
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from different tabs to new sheet

    Quote Originally Posted by Abhishekghorpade View Post
    I ran the macro.. Its running but not giving any result nor giving any error message.
    At least One of The other sheets must has data
    in A1 date like (1/1/2019) , in range (G1: G5) for example any data
    and in cells H1 any thing (No empty)
    Last edited by salim hasan; Sep 20th, 2019 at 04:32 PM.

  5. #5
    Board Regular
    Join Date
    Oct 2018
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from different tabs to new sheet

    Quote Originally Posted by salim hasan View Post
    At least One of The other sheets must has data
    in A1 date like (1/1/2019) , in range (G1: G5) for example any data
    and in cells H1 any thing (No empty)
    >H1 has the data in all the sheets
    > Date format in column A is DD/MM/YYYY
    Still the macro is not pulling any data to data_store tab
    I can give you the file if that helps you

  6. #6
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from different tabs to new sheet

    Quote Originally Posted by Abhishekghorpade View Post
    >H1 has the data in all the sheets
    > Date format in column A is DD/MM/YYYY
    Still the macro is not pulling any data to data_store tab
    I can give you the file if that helps you
    Type any thing in column(G) then try again

  7. #7
    Board Regular
    Join Date
    Oct 2018
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from different tabs to new sheet

    Quote Originally Posted by salim hasan View Post
    Type any thing in column(G) then try again
    Column G has data on daily basis from very long time.

  8. #8
    Board Regular
    Join Date
    Oct 2018
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from different tabs to new sheet

    Column A has Dates.. Column G has values and cell H1 has unique number

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •