Blank lines not "blank", creats many empty lines.

[MarkBeck]

Hi

The code below takes data from individual work cards (i.e., sheets("AD") below), and transfers it to a “central” sheet. Although the entire card is copied and pasted [Range("C1639")], not all lines are actually populated. The next paste looks for the first blank line in the “central” sheet [End(xlDown)], and pastes new data there.

However, even if only three lines were populated, the next paste will start at line 41! It seems that the lines in column "A" are not "really blank". This creates many blank lines in the central sheet.

Also note, that I included a [‘] at my MsgBox, since I keep getting a “compile error, expected value =”. I want to give the people the chance NOT to proceed with the transfer.

Here is my code;

Sub TransferData()
' msgbox("This action cannot be undone, and will transfer your data and blank your sheet.",vbOKCancel,"data transfer")

ActiveSheet.Unprotect
Sheets("Central").Visible = xlSheetVisible
Range("C1639").Select
Selection.Copy
Sheets("Central").Activate
ActiveSheet.Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Central").Visible = xlSheetVeryHidden
Sheets("AD").Select
Range("F13").ClearContents
Range("D16:F39").ClearContents
Range("J1639").ClearContents
Range("E13").Select
ActiveSheet.Protect
MsgBox ("Please select week from drop-down list!")
End Sub

 

[strongm]

You migth actually want to ask this in forum707

 

[MarkBeck]

Thanks

Did so.