VBA - copying rows with data

[timmetal]

I currently have multiple monthly spreadsheets named "receipts Jan", "receipts Feb" etc. and currently writing a code which will open each spreadsheet to copy and paste data from row 19 only and then data from row 28 onward into one consolidated spreadsheet named "consolidated data". However, the data from row 28 may contain 1 row or 10 rows so I am unable to specify a particular range in my code, is there a way I can code it such it will only copy rows with values?

Secondly, as I will be pasting the data into the consolidated spreadsheet is it possible to code it such that the data from "receipt Feb" will be pasted directly one row below "receipt Jan"?

Hope I haven't confused anyone, thanks in advance!

 

[vcoolio]

Hello Timmetal,

Perhaps the following code may help:-

Sub Test()

Dim ws As Worksheet, sh As Worksheet, lr As Long, lr1 As Long, sr As Long

Set sh = Sheets("Consolidated Data")

Application.ScreenUpdating = False

For Each ws In Worksheets
       If ws.Name <> "Consolidated Data" Then
       lr = ws.Range("A" & Rows.Count).End(xlUp).Row
       ws.Range("A19").EntireRow.Copy sh.Range("A" & Rows.Count).End(3)(2)
       ws.Range("A28:A" & lr).EntireRow.Copy sh.Range("A" & Rows.Count).End(3)(2)
       sr = sh.Cells(sh.Rows.Count, "I").End(xlUp).Row + 1
       lr1 = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
       sh.Range("I" & sr & ":I" & lr1) = ws.Name
       End If
Next ws
       
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

As I don't know the extent of your data set, I've assumed the following:-

- Your data in each sheet starts in row2 with headings in row1.
- The Column range in all source sheets is A - H.
- The Column range in the Consolidated Data sheet is A - I with Column I being the column where the source sheet names are placed.

Secondly, as I will be pasting the data into the consolidated spreadsheet is it possible to code it such that the data from "receipt Feb" will be pasted directly one row below "receipt Jan"?

As long as your worksheets are in the correct order, this will happen with the code above.

I hope that this helps.

Cheerio,
vcoolio.