I have a macro (Excel 2010) that pulls a specific cell from multiple tabs and pastes it into a 'Tables' tab. The 'Tables' tab looks in column B for the next empty cell, puts in the date I have it grabbing from and then the data from all the multiple tabs going across (so in c, d, e, f, g...). Sometimes there is no data in a tab/s so they appear blank when the macro is run.

When I run the Macro again, it puts the date in column B like it should (in the next empty cell), but if there was no data for C in the previous run, the new data gets pasted in the above column.
In other words 1st Macro Run: B3 Date, C3 blank, D3##, E3##..
2nd Macro Run: B4 Date, C3##, D4##, E4##...

I don't want the macro to paste the data from the multiple tabs in the cell above if it's empty. I want it to paste across based on where B is entered. Below is my current macro.

Sub DataPull()
'PERFORMANCE DATA PULL: copies date from tab C1
Application.ScreenUpdating = False
Dim rng, dest As Range
Worksheets("Tables").Activate
Set rng = Range("A1")
rng.Copy
Worksheets("Tables").Activate
Set dest = Cells(Rows.Count, "b").End(xlUp).Offset(1, 0)
dest.PasteSpecial Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
'copies Performance Efficiency % (J.) from specified tab
Application.CutCopyMode = False
Worksheets("C1").Activate
Set rng = Range("G42")
rng.Copy
Worksheets("Tables").Activate
Set dest = Cells(Rows.Count, "c").End(xlUp).Offset(1, 0)
dest.PasteSpecial Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'copies Performance Efficiency % (J.) from specified tab
Application.CutCopyMode = False
Worksheets("C2").Activate
Set rng = Range("G42")
rng.Copy
Worksheets("Tables").Activate
Set dest = Cells(Rows.Count, "d").End(xlUp).Offset(1, 0)
dest.PasteSpecial Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'copies Performance Efficiency % (J.) from specified tab
Application.CutCopyMode = True
Worksheets("C3").Activate
Set rng = Range("G42")
rng.Copy
Worksheets("Tables").Activate
Set dest = Cells(Rows.Count, "e").End(xlUp).Offset(1, 0)
dest.PasteSpecial Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'copies Performance Efficiency % (J.) from specified tab
Application.CutCopyMode = True
Application.CutCopyMode = True
Worksheets("C4").Activate
Set rng = Range("G42")
rng.Copy
Worksheets("Tables").Activate
Set dest = Cells(Rows.Count, "f").End(xlUp).Offset(1, 0)
dest.PasteSpecial Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'copies Performance Efficiency % (J.) from specified tab
Application.CutCopyMode = True
Application.CutCopyMode = True
Worksheets("C5").Activate
Set rng = Range("G42")
rng.Copy
Worksheets("Tables").Activate
Set dest = Cells(Rows.Count, "g").End(xlUp).Offset(1, 0)
dest.PasteSpecial Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.ScreenUpdating = True
End Sub