Stumped: Need copy-paste solution within date range

PhilRJ

New Member
Joined
Jun 12, 2015
Messages
13
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
One can use the advanced filter to do this quite easily.
I'll set up a sample for you to see/use...
 
Upvote 0
The code you can use is as follows:

Code:
Sub GetData()
    Sheets("Output").Range("A5").CurrentRegion.Clear
    Sheets("Input").Range("A1").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A1:B2"), _
        CopyToRange:=Sheets("Output").Range("A5"), Unique:=False
End Sub

See this sample workbook to refer to the Criteria sheet, which must be set up as indicated for the advanced filter to use...
 
Last edited:
Upvote 0
Rudi,

Sorry for the delayed response.
It took me a while to fully appreciate the brilliance of this little piece of code.
Very impressed. Good Job! It worked like a charm for my project.

Cheers,

Phil

Btw: What books or resources do you suggest for a VBA beginner such as myself?
 
Upvote 0
Hi Phil,

Glad I could help...

There is no shortage of VBA books (online e-books or paper-based) that one can purchase and review. Books that are tutorial based are the best as they offer step by step guidance and practice files that you can work with. I have used many of these for teaching purposes which includes one called Programming with Excel VBA Step-by-Step and also Excel VBA for Dummies. (BTW: the dummies books are excellent as they are written as a self study aid).

HOWEVER: Before you go out and spend money on books, I can definitely recommend that you work through these two free online courses (links below). You will get as much from these as you would if you had to buy the books. In addition to the two online courses, also have a look at the Excel 2010 VBA Tutorial on Youtube (link below). Lots of short videos that guide you through the fundamentals of VBA and the need to knows!

BTW: Just frequenting the forums on VBA topics and interacting with the real world examples will also be a great aid to learning VBA.
Happy studying... :)

- Excel VBA For Complete Beginners
- Free VBA Course
- Excel 2010 VBA Tutorial (Youtube videos)
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top