Hi folks,
I'm new to VBA (started a few days ago), so I'm really struggling with my code.
I need the following macro:
1) Within the same workbook I have 2 spreadsheets (one named "input" the other "output")
2) The spreadsheet named input contains about 300 lines of sales date for a given product under 7 different columns.
The fifth column informs dates (ex: 05/30/2015), indicating when a given transaction took place.
3) In the output spreadsheet, I have the same 7 column headers along row 5 (the first column header is in cell A5). The rest of the sheet is blank (below the column headers). In cell B2 I have a start date and in cell B3 I have an end date.
The problem: I'd like to produce a macro that sifts through the 300 lines of date in spreadsheet "input" within a given date range (startdate = B2 and enddate = B3) and posts the data just below the column headers in the output spreadsheet. Before pasting, however, I'd like to clear the contents of the cells below the column headers in spreadsheet input (ie, below row 5). This will allow me to run transaction data for different dates on a clean slate everytime.
This is what I have so far.
Sub BoletoSAP()
Application.ScreenUpdating = False
Dim StartDate As Date
StartDate = Sheets("output").Range("B2").Value
Dim EndDate As Date
EndDate = Sheets("output").Range("B3").Value
x = 2
Do While Cells(x, 2) <> ""
If Sheets("input").Cells(x, 5) >= StartDate And Sheets("input").Cells(x, 5) <= EndDate Then
Worksheets("input").Rows(x).Copy
Worksheets("output").Activate
erow = Sheets("output").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("output").Rows(erow)
End If
Worksheets("input").Activate
x = x + 1
Loop
Worksheets("output").Select
End Sub
------------------------------------------
The code won't work.
I don't know what's wrong.
As for the clear contents below the column headers (row 5) before running the loop, not sure how that's done.
Feel free to give it a shot.
Much appreciate any help.
Cheers,
Phil
I'm new to VBA (started a few days ago), so I'm really struggling with my code.
I need the following macro:
1) Within the same workbook I have 2 spreadsheets (one named "input" the other "output")
2) The spreadsheet named input contains about 300 lines of sales date for a given product under 7 different columns.
The fifth column informs dates (ex: 05/30/2015), indicating when a given transaction took place.
3) In the output spreadsheet, I have the same 7 column headers along row 5 (the first column header is in cell A5). The rest of the sheet is blank (below the column headers). In cell B2 I have a start date and in cell B3 I have an end date.
The problem: I'd like to produce a macro that sifts through the 300 lines of date in spreadsheet "input" within a given date range (startdate = B2 and enddate = B3) and posts the data just below the column headers in the output spreadsheet. Before pasting, however, I'd like to clear the contents of the cells below the column headers in spreadsheet input (ie, below row 5). This will allow me to run transaction data for different dates on a clean slate everytime.
This is what I have so far.
Sub BoletoSAP()
Application.ScreenUpdating = False
Dim StartDate As Date
StartDate = Sheets("output").Range("B2").Value
Dim EndDate As Date
EndDate = Sheets("output").Range("B3").Value
x = 2
Do While Cells(x, 2) <> ""
If Sheets("input").Cells(x, 5) >= StartDate And Sheets("input").Cells(x, 5) <= EndDate Then
Worksheets("input").Rows(x).Copy
Worksheets("output").Activate
erow = Sheets("output").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("output").Rows(erow)
End If
Worksheets("input").Activate
x = x + 1
Loop
Worksheets("output").Select
End Sub
------------------------------------------
The code won't work.
I don't know what's wrong.
As for the clear contents below the column headers (row 5) before running the loop, not sure how that's done.
Feel free to give it a shot.
Much appreciate any help.
Cheers,
Phil