Copy cells in a row if not blank to another sheet

realred2

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I have a section of one page that has 3 columns of info pulled from multiple cells in other places on the page, not all the rows in this section have anything in them. I want to pull all the lines with something in them to a second page of the same workbook with all the info in staying in line. Is this a VBA job or just some formulas and a ctrl+shft+enter job?

IE: my info is in AF13:AH1291 (Named "CS_INFO") on page "ADD MTRX", and if the row has information in it, I want to copy it to sheet "CS INFO" starting at location J21 without any of the blank rows.

After it's put on the "CS INFO" sheet, I need to output it to a tab delimited rich text file. (I think this I can do with VBA and a button when I need the output). I had figured out how to do this 10 years ago at a different company. Now I've forgotten again. If you can help, I would appreciate it, if you can explain it to me, I'd be very grateful!
Thanks,
Rob
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

realred2

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I started with some code I found for a VBA way, and changed what I could to fit my needs, but I am having a problem where it is copying the formating (Outline and background color (and text color of only one column) and moving it all to the 2nd sheet. If I select the cell, I can see the text in the formula bar, but I can't see it in the actual selected cell. Can anyone help?

VBA Code:
Sub test()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range, rngToCopy As Range
    Dim lastrow As Long
    'change Sheet1 and Sheet2 to suit
    Set ws1 = ThisWorkbook.Worksheets("ADDRESS MATRIX")
    Set ws2 = ThisWorkbook.Worksheets("CS INFO SHEET")

    With ws1
        'assumung that your data stored in column A:B, Sheet1
        lastrow = .Cells(.Rows.Count, "AF").End(xlUp).Row
        Set rng = .Range("AF13:AI" & lastrow)
        'clear all filters
        .AutoFilterMode = False
        With rng
            'apply filter
            .AutoFilter Field:=4, Criteria1:="x"
            On Error Resume Next
            'get only visible rows
            Set rngToCopy = .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
        'copy range
        If Not rngToCopy Is Nothing Then rngToCopy.Copy Destination:=ws2.Range("J21")
        'clear all filters
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
End Sub
 

realred2

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I see that the VBA is copying the formatting and conditional formatting from the other page to the new page, can we copy only the values? And I'd like to clear the range that is being pasted before pasting the new info into it. Can I do something that does that at the start of the VBA code?

clearrange.range("J21:M1300") = yes or something similar?

Thanks
 

AC PORTA VIA

Board Regular
Joined
Apr 9, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
maybe this
VBA Code:
ws2.Range("J21:M1300")).ClearContents
and
change this line
Code:
If Not rngToCopy Is Nothing Then rngToCopy.Copy Destination:=ws2.Range("J21")
to this
Code:
If Not rngToCopy Is Nothing Then rngToCopy.Copy
        ws2.Range("J21").PasteSpecial Paste:=xlPasteValues
 
Solution

realred2

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

I'm going to put my whole code here for any future references to this question, But Thank You AC PORTA VIA!
This worked perfectly.


VBA Code:
 Sub test()
    Worksheets("CS INFO SHEET").Range("J21:M1300").ClearContents
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range, rngToCopy As Range
    Dim lastrow As Long
   'change Sheet1 and Sheet2 to suit
    Set ws1 = ThisWorkbook.Worksheets("ADDRESS MATRIX")
    Set ws2 = ThisWorkbook.Worksheets("CS INFO SHEET")
    With ws1
       'assumung that your data stored in column A:B, Sheet1
        lastrow = .Cells(.Rows.Count, "AF").End(xlUp).Row
       Set rng = .Range("AF13:AI" & lastrow)
       'clear all filters
        .AutoFilterMode = False
        With rng
           'apply filter
            .AutoFilter Field:=4, Criteria1:="x"
            On Error Resume Next
           'get only visible rows
            Set rngToCopy = .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
       'copy range
        If Not rngToCopy Is Nothing Then rngToCopy.Copy
                    ws2.Range("J21").PasteSpecial Paste:=xlPasteValues
       'clear all filters
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
 End Sub

Now to create a button for "Save as tab delimited RTF" File.

Thank You!
 

realred2

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
So I noticed a slight glitch, the page that it pulls info from (ADDRESS MATRIX) has a couple VBA things to hide rows, and when working on that page only, they work just fine, but when I go to use this VBA code (test) on the new page (CS INFO SHEET), it will un-hide all the rows that were previously hidden on the "ADDRESS MATRIX" page.

This is probably not a huge issue, as this Code will be used after I'm done on the previous page, but I do sometimes have to go back to it. Is there anyway to have it not un-hide all my rows on the "ADDRESS MATRIX" sheet?
 

AC PORTA VIA

Board Regular
Joined
Apr 9, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
Hmm
Not sure about that
Hopefully somebody who knows more than I do can provide you with good answer
 

Forum statistics

Threads
1,141,608
Messages
5,707,374
Members
421,506
Latest member
TekillaSunrize

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