Copy and Paste Specific Filtered Records to Another Workbook/Worksheet

[reberryjr]

I'm trying to run through a list of customer accounts. If there have been 0 transactions for a set period of time, I want a particular message displayed. If there have been some transactions, then I want to copy and paste details from a data file, onto their document template. The issue that I'm finding is only copying the visible data from the filter, onto the template. I've googled copying filtered cells onto another worksheet, and I haven't seen anything that explains what I'm trying to do.

Example, if I filter, and the data I'm trying to copy starts in row 7 and runs through row 15, I would want to copy C7:C15, and paste it into A60 on the template.

For Each c In Rng
    If c <> "" Then
        c.Copy
        sMS1.Range("K7").PasteSpecial xlPasteValues
        If sMS1.Range("G58").Value < 1 Then
        sMS1.Range("A60").Value = "No transaction activity for this statement period."
        fName = sMS1.Range("K7").Value & ".xlsx"
        s.SaveAs fPath & fName
        'Next
        Else
        'If sMS1.Range("G58").Value > 0 Then
            mTH.UsedRange.AutoFilter field:=1, Criteria1:=sMS1.Range("K7").Value
            mTH.Range("C2" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
                sMS1.Range("A60").PasteSpecial xlPasteValues
            mTH.Range("D2" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
                sMS1.Range("D60").PasteSpecial xlPasteValues
            mTH.Range("E2" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
                sMS1.Range("J60").PasteSpecial xlPasteValues
        End If
      
        fName = sMS1.Range("K7").Value & ".xlsx"
        s.SaveAs fPath & fName
    End If
Next

 

[Fluff]

What is mTHLR?

 

[reberryjr]

What is mTHLR?

The last record on a worksheet. I re-evaluated the last record, and it's coming up correct (at least for the first customer number). For some reason, it's still trying to copy all of the data in the rows, instead of the range I want, which would be C2:C5 in this instance; but could be C27:C28 in another.

 

[Fluff]

Yes, but what exactly is it? How is it declared & how is it assigned a value?

 

[reberryjr]

Yes, but what exactly is it? How is it declared & how is it assigned a value?

Here is where I'm re-evaluating it after the filter. The code is in red font.

For Each c In Rng
    If c <> "" Then
        c.Copy
        sMS1.Range("K7").PasteSpecial xlPasteValues
        If sMS1.Range("G58").Value < 1 Then
        sMS1.Range("A60").Value = "No transaction activity for this period."
        fName = sMS1.Range("K7").Value & ".xlsx"
        s.SaveAs fPath & fName
        'Next
        Else
        'If sMS1.Range("G58").Value > 0 Then
            mTH.UsedRange.AutoFilter field:=1, Criteria1:=sMS1.Range("K7").Value
            [COLOR=rgb(184, 49, 47)]mTHLR = mTH.Range("A" & Rows.Count).End(xlUp).Row[/COLOR]
            mTH.Range("C2" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
                sMS1.Range("A60").PasteSpecial xlPasteValues
            mTH.Range("D2" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
                sMS1.Range("D60").PasteSpecial xlPasteValues
            mTH.Range("E2" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
                sMS1.Range("J60").PasteSpecial xlPasteValues
        End If
      
        fName = sMS1.Range("K7").Value & ".xlsx"
        s.SaveAs fPath & fName
    End If
Next

 

[Fluff]

If you want to highlight parts of your code please use the Rich code tag & not the VBA tags.

As you are assigning a ro number to the variable you need to use

mTH.Range("C2:C" & mTHLR).SpecialCells(xlCellTypeVisible).Copy

 

[reberryjr]

If you want to highlight parts of your code please use the Rich code tag & not the VBA tags.

As you are assigning a ro number to the variable you need to use

mTH.Range("C2:C" & mTHLR).SpecialCells(xlCellTypeVisible).Copy

I am not, as I'm not entirely sure what that is. I guess I'll need to do some more research. Sorry about the incorrect tags.

 

[Fluff]

This line

mTHLR = mTH.Range("A" & Rows.Count).End(xlUp).Row

assignes the row number of the last visible row to your variable.

 

[reberryjr]

This line

mTHLR = mTH.Range("A" & Rows.Count).End(xlUp).Row

assignes the row number of the last visible row to your variable.

So I'm a bit slow. I realized I wasn't setting the range properly with the mTHLR. I thank you for your patience, and your assistance. It "appears" to be working now.

 

[Fluff]

You're welcome & thanks for the feedback.