VBA Code to only copy cells with data within a range

[lebroj]

Dear Experts,

Can someone please help?
In Excel 2010 I have a spreadsheet in which I enter data on one sheet and then the data gets copied to another Spreadsheet. What I would like to do is have the code only copy Cells in range "e5:j55" that only have values instead of all.
So for example one day I may enter 50 records of data, and the next I may enter only 10 records. The amount of records will vary from day to day and will never exceed 50 records.

The current code is :

Dim ACell As String
Dim ASheet As String
  
ACell = ActiveCell.Address
ASheet = ActiveSheet.Name
Range("e5:J55").Copy
Sheets("HistoricalData").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Sheets("HistoricalData").Select
Application.CutCopyMode = False
Range("E5:J55").SpecialCells(xlCellTypeConstants, 23).ClearContents
MsgBox "Historical Data Sheet Has Been Updated" _
, vbInformation + vbOKOnly, "Macro finished"

Worksheets(ASheet).Select
Range(ACell).Select

[mehmetcik]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[lebroj]

Thanks for pointing this out to me. I have made adjustments to the post.

[mehmetcik]

Dim ACell As String
Dim ASheet As String
  
Range("e5:J55").SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("HistoricalData").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Range("E5:J55").SpecialCells(xlCellTypeConstants, 23).ClearContents
MsgBox "Historical Data Sheet Has Been Updated" _
, vbInformation + vbOKOnly, "Macro finished"

[lebroj]

Thanks Mehmetcik, it work as expected with only one issue. Column J has a formula and During the copy/paste it leaves this blank, however if I enter a value in column J manually then the value is copied accordingly.

[mehmetcik]

Try this

Dim ACell As String
Dim ASheet As String
  
Range("e5:J55").SpecialCells(xlCellTypeFormulas, 23).Copy
Sheets("HistoricalData").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

Range("e5:J55").SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("HistoricalData").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Range("E5:J55").SpecialCells(xlCellTypeConstants, 23).ClearContents
MsgBox "Historical Data Sheet Has Been Updated" _
, vbInformation + vbOKOnly, "Macro finished"

[lebroj]

This last code is not providing the desired results, but I reviewed my spreadsheet and made some modifications. Your Code provided on 1/21 works perfectly on my modified sheet. Thanks!