Copying cells from multiple csv's to new workbook: trouble keeping the blanks

[Crooozin]

I am new to vba. This is my first attempt at it.

Here's my current script:


____________________________________________
Sub Macro42()

Dim sPath As String
Dim sFil As String
Dim strName As String
Dim twbk As Workbook
Dim owbk As Workbook
Dim ws As Worksheet

Workbooks.Add

Set twbk = ActiveWorkbook
sPath = "D:\tis et25\rev8\no shift\0\" 'Need user input box
sFil = Dir(sPath & "*.csv")

Do While sFil <> ""
strName = sPath & sFil
Set owbk = Workbooks.Open(strName)
Set ws = owbk.Sheets(1)
'next step: use the column headers in row 1 to pick the correct columns. Requires user input box? Or a dropdown?
ws.Range("BD1", Range("BD20").End(xlUp)).Copy
twbk.Sheets(1).Range("A1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("BE1", Range("BE20").End(xlUp)).Copy
twbk.Sheets(1).Range("B1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("CD1", Range("CD20").End(xlUp)).Copy
twbk.Sheets(1).Range("C1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("CE1", Range("CE20").End(xlUp)).Copy
twbk.Sheets(1).Range("D1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("EG1", Range("EG20").End(xlUp)).Copy
twbk.Sheets(1).Range("E1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("EH1", Range("EH20").End(xlUp)).Copy
twbk.Sheets(1).Range("F1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("EI1", Range("EI20").End(xlUp)).Copy
twbk.Sheets(1).Range("G1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("EN1", Range("EN20").End(xlUp)).Copy
twbk.Sheets(1).Range("H1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("EO1", Range("EO20").End(xlUp)).Copy
twbk.Sheets(1).Range("I1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("FN1", Range("FN20").End(xlUp)).Copy
twbk.Sheets(1).Range("J1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("FO1", Range("FO20").End(xlUp)).Copy
twbk.Sheets(1).Range("K1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("FW1", Range("FW20").End(xlUp)).Copy
twbk.Sheets(1).Range("L1048576").End(xlUp)(2).PasteSpecial xlPasteValues
ws.Range("GF1", Range("GF20").End(xlUp)).Copy
twbk.Sheets(1).Range("M1048576").End(xlUp)(2).PasteSpecial xlPasteValues

'Close no save, maintains integrity of csv files
owbk.Close False

sFil = Dir
Loop

twbk.SaveAs (sPath & "ET25_r8_noshift_0.xls") 'Need to parse sPath to just create this, otherwise requires user input
End Sub
______________________________________


The problem is this. My data has holes; most importantly, in some cases there are blanks in the bottom row. That means the .end statements in the paste steps put the data into a row where there should be a blank. I can't figure out how to fix that.

For instance, in row BD in the first csv file in the stated folder I have this (set of made-up numbers).
Column BD
row1 columnheadername
2 2
3 1
4 99
5 1
6 4
7 99
8 66
9 34
10 42
11 42
12 42
13 42
14 42
15 <blank>
16 <blank>
17 <blank>
18 <blank>
19 <blank>
20 <blank>

When I paste the next set I don't want it to start pasting in row 15, but rather row 21, and so on for each .csv file in the directory (there can be 100's of them, or 10, or 1, or none depending on how our machinery is feeling that day ).

Please help!

(Also, if you have any recommendations for the comments I've made I would welcome them- I haven't started working on those yet though)</blank></blank></blank></blank></blank></blank>