Copy only data in some columns to another sheet

Hello all,
I am trying to copy rows that match a date range but I only want some of the columns to be copied to a pre-defined sheet.

For example, if the row is selected as a match because it is within the desired date range, I want the data in columns 'A-F', 'I-J','N-O' to be copied to the sheet 'Report' starting in 'A3'.

Thanks in advance!!

Andrew

If you'll click on GO ADVANCED, use the paperclip icon to upload a sample workbook showing us a good set of sample data and your report sheet.

Also, make sure you've decided HOW to designate the date or date range to match. Is it cells? Is it a popup question by the macro itself? Let us know.

The copy part is easy, just need to see the specifics.

Also, during the copy job, should the data in the report be completely replaced or just appended to the bottom?

You mention other code/formulas...anything in the Report sheet we need to take into account? Any formulas that need to be extended. Be thorough in your sample.

Distribution formula in column J corrected to make absolute references so it doesn't break when copied to a different column during macro...J3 copied down:

=IF(OR(NOT(ISNUMBER($F3)),ISNA(MATCH($E3,Totals!$A:$A,0))),"",$F3*VLOOKUP($E3,Totals!$A:$C,3,0))

Your sheet was too massive to really pull apart. Instead, I just created a simple macro to grab the data from Distribution between two dates entered and put them on the In_Progress_Report sheet as you had described.

I figured this was enough for you to see a way to copy just the rows you wanted, and perhaps see another way to grab data en masse from one sheet and put it in another without loops.

Option Explicit

Sub CopyMacroSample()
'Copies rows from Distribution list to InProgressReport
'based on date range  (JBeaucaire)

Dim Date1 As Date, Date2 As Date, LR As Long

Date1 = Application.InputBox("What is the start date?", Type:=2)
Date2 = Application.InputBox("What is the end date?", Type:=2)
    
'Clear old report
    Sheets("In_Progress_Report").Range("A3:J" & Rows.Count).Clear

'Filter data based on dates chosen
    Sheets("Distribution").Activate
    LR = Range("A" & Rows.Count).End(xlUp).Row

    Range("A2").AutoFilter Field:=3, Criteria1:=">=" & Date1, Operator:=xlAnd, _
        Criteria2:="<=" & Date2
    
'Copy data ranges and remove autofilter
    Range("A3:F" & LR).SpecialCells(xlCellTypeVisible).Copy Sheets("In_Progress_Report").Range("A3")
    Range("I3:J" & LR).SpecialCells(xlCellTypeVisible).Copy Sheets("In_Progress_Report").Range("G3")
    Range("N3:O" & LR).SpecialCells(xlCellTypeVisible).Copy Sheets("In_Progress_Report").Range("I3")
    Range("A2").AutoFilter

'Review New Report
    Sheets("In_Progress_Report").Activate
    Columns("A:J").Columns.AutoFit
    Range("N1") = Format(Date1, "M/D/YYYY")
    Range("O1") = Format(Date2, "M/D/YYYY")
    Range("A1").Select
End Sub

Hope this helps.

I suppose if we really want to show off, we can replace these three copy lines of code:

Range("A3:F" & LR).SpecialCells(xlCellTypeVisible).Copy Sheets("In_Progress_Report").Range("A3")
Range("I3:J" & LR).SpecialCells(xlCellTypeVisible).Copy Sheets("In_Progress_Report").Range("G3")
Range("N3:O" & LR).SpecialCells(xlCellTypeVisible).Copy Sheets("In_Progress_Report").Range("I3")

...with this ONE line:

Range("A3:F" & LR & ",I3:J" & LR & ",N3:O" & LR).SpecialCells(xlCellTypeVisible).Copy Sheets("In_Progress_Report").Range("A3")

The SpecialCells(xlCellTypeVisible) can only copy the visible rows. Not sure how your mileage would vary.

If the one-liner is too confusing, then staying with the three separate copy lines at least has the benefit of being visually simple.

Code is for hacking, eh!? Glad you got it working. Onward and upward.