VBA to copy data from different tabs to new sheet

Abhishekghorpade

Board Regular
Joined
Oct 3, 2018
Messages
71
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
 

salim hasan

Board Regular
Joined
Dec 25, 2013
Messages
103
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
[SIZE=2][SIZE=1]
[/SIZE][/SIZE]



 
Last edited:

salim hasan

Board Regular
Joined
Dec 25, 2013
Messages
103
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:

Abhishekghorpade

Board Regular
Joined
Oct 3, 2018
Messages
71
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
 

Forum statistics

Threads
1,086,209
Messages
5,388,450
Members
402,118
Latest member
Scott Hughson

Some videos you may like

This Week's Hot Topics

Top