Previous Topic Next Topic

To show blank record when no data while copying

Solved/Closed
AK - Apr 18, 2014 at 03:19 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 22, 2014 at 12:37 AM
Hello,

I have three sheets sheet1(Active) ,Sheet 2(Inactive) and Sheet3(Total).

I have written code which would copy data from Active sheet ,then data from Inactive sheet and paste it in Total Sheet.

Now it is working fine when Active and Inactive sheet is having data but when it is not having data it is simply displaying the header.

Can anybody help me in tweaking my code to show blank (no header ) when there is no data . 
Sub Macro1()
'
' Macro1 Macro
'
Worksheets("Active").Range(Worksheets("Active").Cells(4, 2), Worksheets("Active").Cells(Worksheets("Active").Cells(Worksheets("Active").Rows.Count, "B").End(xlUp).Row, 13)).Copy _
    Destination:=Worksheets("Total Position").Cells(Worksheets("Total Position").Cells(Worksheets("Total Position").Rows.Count, "A").End(xlUp).Row + 1, 1)
Worksheets("Inactive").Range(Worksheets("Inactive").Cells(4, 2), Worksheets("Inactive").Cells(Worksheets("Inactive").Cells(Worksheets("Inactive").Rows.Count, "B").End(xlUp).Row, 13)).Copy _
    Destination:=Worksheets("Total Position").Cells(Worksheets("Total Position").Cells(Worksheets("Total Position").Rows.Count, "B").End(xlUp).Row + 1, 1)
    Sheets("Active").Visible = False
    Sheets("Inactive").Visible = False
    
'
End Sub

3 replies

Answer 1 / 3
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 19, 2014 at 08:20 AM
I have slightly tweaked your macro and called the new macro as "test"

the code in this macro 

If r.Rows.Count > 1 Then

copied only if there is more data than headers

the macro test is 

Sub test()
Dim r As Range, sh(1 To 2) As String, j As Integer
sh(1) = "active"
sh(2) = "inactive"

For j = 1 To 2
With Worksheets(sh(j))
Set r = Range(.Cells(4, 2), .Cells(Cells(Rows.Count, "B").End(xlUp).Row, 13))
'MsgBox r.Address

If r.Rows.Count > 1 Then
r.Copy
End If

With Worksheets("total position")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End With
Next j
MsgBox "macro done"
End Sub
0
AK
Apr 21, 2014 at 10:04 AM
Thanks Venkat its working .I would test and validate with more data and let you know.

Thanks again for your help
0
Answer 2 / 3
AK
Apr 21, 2014 at 10:25 AM
Venkat ,

I checked running with Data ,it is only picking up the first record from both the sheets .I need that it should pick up all data from two sheets and if there is no data then no header display.

Can you please have a look .

Ak
0
Answer 3 / 3
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 22, 2014 at 12:37 AM
send the file through
speedyshare.com
0