Combine To VBA Codes Into One

bradyman97

New Member
Joined
Feb 22, 2008
Messages
33
I advance filter data in sheet called Invoices and filter (First Code Below) it to sheet called Warehouse_Invoices. Then I copy and paste Warehouse_Invoices to sheet WAREHOUSE_Inventory last row plus one. How can I combine the two VBA codes below into one code? I want the new code to paste to the last row plus one.



First Code

Sub Copy_Warehouse_Invoices()

Dim rg As Range
Set rg = ThisWorkbook.Worksheets("Warehouse_Invoices").Range("A7").CurrentRegion

rg.Offset(1).ClearContents

Dim rgInvoices As Range, rgCriteria As Range, rgWarehouse_Invoices As Range


Set rgInvoices = ThisWorkbook.Worksheets("Invoices").Range("A1").CurrentRegion

Set rgCriteria = ThisWorkbook.Worksheets("Warehouse_Invoices").Range("A1").CurrentRegion

Set rgWarehouse_Invoices = ThisWorkbook.Worksheets("Warehouse_Invoices").Range("A7").CurrentRegion

rgInvoices.AdvancedFilter xlFilterCopy, rgCriteria, rgWarehouse_Invoices




Second Code

Sub Filter_Warehouse_Invoices_To_WAREHOUSE_Inventory()


Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim rng As Range

Set sht1 = Worksheets("Warehouse_Invoices")
Set sht2 = Worksheets("WAREHOUSE_Inventory")

With sht1
Set rng = .Range("A8", .Range("A8").End(xlDown))
Set rng = .Range(rng, rng.End(xlToRight))

End With

rng.Copy

sht2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

GlennJ

New Member
Joined
Mar 26, 2018
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
If the code is all in one Workbook you can just Call the second macro.
VBA Code:
Sub Copy_Warehouse_Invoices()

Dim rg As Range
Set rg = ThisWorkbook.Worksheets("Warehouse_Invoices").Range("A7").CurrentRegion

rg.Offset(1).ClearContents

Dim rgInvoices As Range, rgCriteria As Range, rgWarehouse_Invoices As Range


Set rgInvoices = ThisWorkbook.Worksheets("Invoices").Range("A1").CurrentRegion

Set rgCriteria = ThisWorkbook.Worksheets("Warehouse_Invoices").Range("A1").CurrentRegion

Set rgWarehouse_Invoices = ThisWorkbook.Worksheets("Warehouse_Invoices").Range("A7").CurrentRegion

rgInvoices.AdvancedFilter xlFilterCopy, rgCriteria, rgWarehouse_Invoices


'This will start the your next macro
Call Filter_Warehouse_Invoices_To_WAREHOUSE_Inventory()


End sub



Sub Filter_Warehouse_Invoices_To_WAREHOUSE_Inventory()


Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim rng As Range

Set sht1 = Worksheets("Warehouse_Invoices")
Set sht2 = Worksheets("WAREHOUSE_Inventory")

With sht1
Set rng = .Range("A8", .Range("A8").End(xlDown))
Set rng = .Range(rng, rng.End(xlToRight))

End With

rng.Copy

sht2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End sub
 

bradyman97

New Member
Joined
Feb 22, 2008
Messages
33
Is there a away to not use sheet called Warehouse_Invoices and have the filter go straight to WAREHOUSE_Inventory to the Last row plus one. So I can eliminate that step.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,370
Messages
5,635,860
Members
416,886
Latest member
coreyalaurence37

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