Copy and Paste Specific Filtered Records to Another Workbook/Worksheet

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
What is mTHLR?
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Yes, but what exactly is it? How is it declared & how is it assigned a value?
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
mTH.Range("C2:C" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
This line
VBA Code:
mTHLR = mTH.Range("A" & Rows.Count).End(xlUp).Row
assignes the row number of the last visible row to your variable.
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
This line
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

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
Top