how to exclude a column from copy and past in vba

Dear Sir,

I have this VBA

Sub Button7_Click()
Application.ScreenUpdating = False
Sheets("resturant").Range("A6:j600000").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Range("A1:C2"), CopyToRange:=Range( _
"A6:j600000"), Unique:=False

Application.ScreenUpdating = True

End Sub

I need to filter and copy range A6:J600000 BUT I NEED TO EXCLUDE CELL "G" FROM FILTERING AND COPYING AND PASTING, BECAUSE I HAVE MY OWN FORMULA IN CELL "G" OF THE COPY TO RANGE and do not need the value of this cell.


Please advice kindly

hello, many thanks for your help, I have included the workbook

again I just need to filter and copy & paste this range a6:j60000 but also omitting cell ( G ) from copying and pasting, is there such a command that I can add to this Range("A6:j600000") to disable copying and pasting of ONLY (G) cell in this Range("A6:j600000") ?

The reasoner for doing so, is that I need to have my own formula in Cell G in the balance sheet, thus stopping this vba from pasting into it and erasing my formulas, but also I need all the other cell values form a6:j60000 to be copied and pasted.

Many thanks

Mark Swan

My dear @Lewis Clark:
I look at your workbook and I look at your VBA code and I come to one conclusion: things will never work for you this way. Let's analyze together:

a) You have defined the criteria range as A1:C2 but C1:C2 is a merged cell. Conclusion: this can't work like this.

b) The range A1:B2 is empty: How do you want us to know which criteria you want to apply?

c) The VBA code you have developed wants to copy the extract range over the data range: Impossible!

I am uploading to the Forum what you should have uploaded: only what is necessary.

After:
a) Complete the 7 data rows with more or less real (or invented) data.

b) Show us in the criteria range a set of actual criteria you would use.

c) Upload said workbook for us to see.

Dear Sir,

we have miss understanding,



a) You have defined the criteria range as A1:C2 but C1:C2 is a merged cell. Conclusion: this can't work like this. ( c1:c2 are not merged together) the criteria range is on the balance sheet, you are looking at the suppliers sheet of which the vba extract data from to balance sheet.

b) The range A1:B2 is empty: How do you want us to know which criteria you want to apply?

again you are looking at the suppliers sheet of which I enter data, the range b2 is not empty on the balance sheet, of which I enter the criteria I want.
and my vba is fully working as is.

c) The VBA code you have developed wants to copy the extract range over the data range: Impossible!

No again you haven't seen the balance sheet in which I enter the criteria I want and extract data from suppliers sheet to balance sheet.


the vba in the workbook I sent you is fully working and extracting info.


my question is simple (can I use this vba, without extracting the cell G data in suppliers sheet? in other words exclude it from the range, while being able to extract all the other data in the range cells?


this is simply my question, the vba is working in the workbook I posted.

Many thanksvba.xlsb

My dear markswan111:

a) 'G' is not a cell: It's a column!... So we're talking about column G, okay?

b) In addition to column G you have formulas in column H.

c) When we use the advanced filter, the formulas are ALWAYS lost and only the values are copied.

d) And if we applied two advanced filters: one copying the data from columns A:F and the other copying the data from columns I:J you would see that in columns G and H you would have nothing and you would have to add the formulas to them.

e) The following solution does a single filtering and then adds the formulas:

Please Login or Register  to view this content.

I forgot to tell you to take a close look at the above VBA code:

a) Every time a range of cells is referenced, said range is preceded by the indication of the sheet to which said range belongs.

b) In this way, any type of doubt is avoided and the original confusion is not given place.

c) And another advantage is that you can have any sheet selected and the code will work the same.

It's amazing that you're showing your original code again after I suggested that you prepend Range() with the name of the sheet the range belongs to (and I explained that's what caused the confusion).

So you have two tasks:

a) Upload the VBA code to the Forum by prepending the name of the associated sheet (your code references a sheet called 'restaurant' that doesn't exist in your entire workbook: Correct that too!),

b) Confirm if there are one or two columns that you want to appear blank.

Originally Posted by markswan111

2- I need only Column (G) to appear Blank

Then use the following variant:

Please Login or Register  to view this content.

PHP Code: 

Sub Macro82()
Dim ws1 As Worksheet, ws2 As Worksheet, LR&
Rem --------------------------\
Set ws1 = Sheets("suppliers")
LR = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Set ws2 = Sheets("ballances")
Rem --------------------------\
Application.ScreenUpdating = False
ws1.Range("A6:J" & LR).AdvancedFilter 2, ws2.Range("A1:C2"), ws2.Range("A6:J6"), False
Rem --------------------------\
LR = ws2.Cells(Rows.Count, 1).End(xlUp).Row
If LR > 6 Then ws2.Range("G7:H" & LR).ClearContents
Application.ScreenUpdating = True
End Sub 


Originally Posted by markswan111

2- I need only Column (G) to appear Blank

Originally Posted by markswan111

... this command in the VBA you provided (If LR > 6 Then ws2.Range("G7:H" & LR).ClearContents) is clearing contents in Column G, thus it is clearing the Formula I have entered in Column G as well, which defeated my purpose of the VBA.

Have you noticed that you first asked for one thing and then asked for the opposite, right?...
_______________________________________________

From the macro that I already showed you, you have two options:

Option 1. Instead of "cleaning" column G have the macro fill that column with whatever formula you want.

Option 2. Starting the macro will save the formula. And that formula will be reset at the end of the macro instead of clearing column G.

Choose you please.

And I don't want to forget to mention that in none of the three workbooks that you uploaded to the Forum can you see a formula in column G of the 'balances' sheet...