Modify my code to copy on certain columns

[Robjwalters]

I have this code (which I did not write, but did modify to make it work on my sheet)and it works great now.

I want to know two things:

if instead of copying the entire row to a new sheet, can I adjust it to only copy Columns B,C,E,G.

I want it to clear the sheet before it copies in the new data.

I have tried to change it but no luck...

[
Option Explicit

Sub CopyRows()
Dim bottomL As Integer
bottomL = Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row

Dim c As Range
For Each c In Sheets("Data").Range("C1:C" & bottomL)
If c.Value = "True" Then
c.EntireRow.Copy Worksheets("ToBeRemediated").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next c

End Sub
]

 

[Smallman]

Hi Rob

Something like the following should set you right. All in one movement this avoids looping.

Option Explicit
Sub copyIt()
Dim lr As Long
Dim rng As Range
Dim sh As Worksheet
 
    Set sh = Sheets("Data")
    lr = sh.Range("C" & Rows.Count).End(xlUp).Row
    Set rng = Union(sh.Range("B2:C" & lr), sh.Range("E2:E" & lr), sh.Range("G2:G" & lr))
    sh.Range("C1:C" & lr).AutoFilter 1, "True"
    rng.Copy Sheets("ToBeRemediated").Range("A" & Rows.Count).End(xlUp)(2)
    sh.[c1].AutoFilter
End Sub

Take care

Smallman

 

[Robjwalters]

thanks,that works great.
I added a question maybe you could help with also, I want to clear the destination sheet before it copies. I have tried to add a clear statement ...with no luck

 

[kchiba]

Hi Smallman,

I like your code, what does the (2) do at the end of rng.Copy line?

 

[Smallman]

Hi Kanti

The (2) at the end is the same as writing offset(1) without all the fuss of typing. It is a little couter intuative as (1) produces the last row and if you leave it out all together it is the last row also. But (2) will always give you the first blank row of the destination sheet.

Take care

Smallman

 

[Smallman]

Hi Rob

Use something like this;

Sheets("ToBeRemediated").[a1].CurrentRegion.Offset(1).ClearContents

Take care

Smallman

 

[Robjwalters]

Wow, that is awesome. I was trying a range and clear contents with no luck. So your code says: Sheets("ToBeRemediated")= Sheet to clear
[a1]= range
CurrentRegion= ????
Offset(1)= do next
ClearContents=clear

Is that correct???

 

[kchiba]

Thanks Smallman.

kanti

 

[Smallman]

Hi Rob

The Current Region is just the block of data which you are working with. It works well when there are no whole lines blank or columns blank. Ideally a model should have none of these blanks so the current region will work beautifully. However if there is whole blank rows/columns it won't work so well. It is all about how you set up your file.

Take care

Smallman

 

[Robjwalters]

Thanks for the insight.

Now I tried to have it copy cells B1-B5 on the summary sheet to C1-C5. This will allow me to compare this week with last week.

I can get it to copy the formulas, but I want the values only and I want it to copy those first before it clears and pastes new values.

Here is what I added:
[
Sheets("Summary").Range("B1:B5").Copy _
Destination:=Sheets("Summary").Range.Value = ("C1")
]

 

[Smallman]

Hi Rob

To do what you want try and remember this simple rule when you are copying

1. If you want values and formulas use one line of code.

Sheets("Summary").Range("B1:B5").Copy Sheets("Summary").Range("C1")

2. If you are copying values, formats etc. then you need to use 2 lines of code to get over the line.

Sheets("Summary").Range("B1:B5").Copy
Sheets("Summary").Range("C1").PasteSpecial xlPasteValues

Notice how there is no break line so it is spread over two lines. The last part xlPasteValues can be changed for formats etc.

It is a very simple rule and I still use it to this day.

Take care

Smallman

 

[Robjwalters]

Thanks yet again, you make it look so easy....