Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

To show blank record when no data while copying [Solved/Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 -
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
See more

3 replies

Answer 1 / 3
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
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
Report

Thanks Venkat its working .I would test and validate with more data and let you know.

Thanks again for your help
Answer 2 / 3

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
Answer 3 / 3
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
send the file through
speedyshare.com
Ask a question