Sub GetInfo()'Dim current workbook
Dim filename As String, User As String
Dim nameMonth As String, nameYear As String, lastDay As String, firstDay As String
Dim ClearRng As Range, wb As Workbook, ws As Worksheet, wsCont As Worksheet
'Set Sheets for current workbook
Set wb = Workbooks("Monthly Tax Report")
Set ws = wb.Worksheets("Monthly Tax Report")
Set wsCont = wb.Worksheets("Continuation")
'Dim new workbook
Dim lrow1 As Long, lrow2 As Long, lrow3 As Long
Dim xl0 As New Excel.Application, xlw As New Excel.Workbook
Dim srcName1 As String, srcName2 As String, srcName3 As String
Dim srcSht1 As Worksheet, srcSht2 As Worksheet, srcSht3 As Worksheet
Dim rng1 As Range, rng2 As Range, rng3 As Range
'Get first and last day of month to use in filename
nameYear = Worksheets("Monthly Tax Report").TextBox1.Text
nameMonth = Worksheets("Monthly Tax Report").ComboBox1.Text
'Format date for filepath
firstDay = Format("1/" & nameMonth & "/" & nameYear, "yyyy-mm-dd")
lastDay = Format(dhLastDayInMonth("1/" & nameMonth & "/" & nameYear), "yyyy-mm-dd")
'File Name for Sales History Download
User = Environ("UserName")
filename = "C:\Users\" & User & "\Downloads\VinoShipper Sales " & _
firstDay & " - " & lastDay & ".xlsx"
'Make sure filepath is valid
If Dir(filename, vbDirectory) = "" Then
MsgBox "The filepath: " & vbCrLf & _
filename & _
vbCrLf & " is not valid. Make sure you have downloaded the correct file."
Exit Sub
End If
'Sheet names - subject to change
srcName1 = "Winery Permit Sales"
srcName2 = "VinoShipper Permit Sales"
srcName3 = "Product Performance"
'Open downloaded file and Set Worksheets
Set xlw = xl0.Workbooks.Open(filename)
Set srcSht1 = xl0.Sheets(srcName1)
Set srcSht2 = xl0.Sheets(srcName2)
Set srcSht3 = xl0.Sheets(srcName3)
'Find last row for each sheet
lrow1 = srcSht1.Range("A1").End(xlDown).Row
lrow2 = srcSht2.Range("A1").End(xlDown).Row
lrow3 = srcSht3.Range("A1").End(xlDown).Row
Dim sh1 As Worksheet, cnt As Long, i As Long, r As Long
Set sh1 = wb.Sheets("Sheet3")
lr = srcSht2.Cells(Rows.Count, 1).End(xlUp).Row
r = 1
For i = 2 To lr Step 24
cnt = srcSht2.Range(srcSht2.Cells(i, 1), srcSht2.Cells(i, Columns.Count).End(xlToLeft)).Cells.Count
srcSht2.Cells(i, 1).Resize(24, cnt).Copy sh1.Cells(r, 1)
cnt = ""
r = r + 35
Next
xlw.Save
xlw.Close
Set xl0 = Nothing
Set xlw = Nothing
End Sub