How do i combine macro module with the rest of the code

Question

I'm a beginner, so any help is much appreciated, I want to combine this macro with the first code, but I don't know how to do that or where to put it.

this is the first code (it has a mistake in it, but I already have an answer on how to fix it, so it's alright):

Sub foo()

Dim ws As Worksheet: Set ws = Sheets("inbd")
Dim wsDestination As Worksheet: Set wsDestination = Sheets("test")

LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("A1:N" & LastRow).AutoFilter Field:=1, Criteria1:=Worksheets("test").Cells(1, 26).Value
    ws.Range("f2:f" & LastRow).SpecialCells(xlCellTypeVisible).Copy Range("C6")
DestinationRow = wsDestination.Cells(wsDestination.Rows.Count, "C").End(xlUp).Row + 1
    wsDestination.Range("C" & DestinationRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False
ws.Range("A1:N" & LastRow).AutoFilter Field:=1




End Sub

currently the first code filters and copies table data in the parameter that I want into another worksheet, but I need a more complex version of the copy so I recorded it in macro, which is super long and looks like this:

Sub Macro8()
'
' Macro8 Macro
'

'
Sheets("INBD").Select
Range("Table1[Description]").Select
Selection.Copy
Sheets("Sheet1").Select
Range("Table19[Description]").Select
ActiveSheet.Paste
Range("D18").Select
Sheets("INBD").Select
Range("Table1[Invoice Date]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("Table19[Invoice '#]").Select
ActiveSheet.Paste
Sheets("INBD").Select
Range("Table1[Invoice '#]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("Table19[Invoice '#]").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Sheets("INBD").Select
Range("Table1[HS Code]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("Table19[HS Code]").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Sheets("INBD").Select
Range("Table1[Unit]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("Table19[M. Unit]").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Range("Table19[Description]").Select
Application.CutCopyMode = False
Selection.Copy
Range("E13").Select
ActiveSheet.Paste
Sheets("INBD").Select
Range("Table1[QTY]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("Table19[QTY]").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Sheets("INBD").Select
Range("Table1[Unit Price]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("Table19[Unit Price]").Select
ActiveSheet.Paste
Sheets("INBD").Select
Range("Table1[Curr.]").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("Table19[Curr]").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Rows("13:22").Select
Rows("13:22").EntireRow.AutoFit
Selection.RowHeight = 30
Application.CutCopyMode = False
With Selection
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

What this does is that it copies values into a table, into specific columns, below the table I wrote in a bunch of stuff and made the color of the font white, so that when it copies, the table moves the cells down hence not altering anything below the table and leaves some space in between. After this I'm going to record a macro which deletes all rows in the table and any other data in the table to clear the document for a new entry.

You seem to want a lot of things at once as a beginner. Maybe consider: Breaking your question up in decent sized subquestions so it is A) easier to ask a question and B) easier to answer and help you out. This will also help you understand what is going on in your code. — JvdV, Aug 06 '18 at 08:30
Ok, You have posted a lot of code and you have posted what you want to do, and that is good. But the title of your question is *How do i combine macro module with the rest of the code* and you have not posted anything related to it. What problems are you having? Where ayou stuck? Any executions errors? What line? What error raises? That's what you have to post and we can help. — Foxfire And Burns And Burns, Aug 06 '18 at 09:15
About your second macro, check [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Foxfire And Burns And Burns, Aug 06 '18 at 09:20
Basically I want to write in something like "run_Macro8" into the first code, but I don't know the syntax or how the code gets altered. I don't even know how to ask the question correctly, meaning, I don't actually need to alter the macro, I just want to use it and I don't know how — Joe Royson, Aug 06 '18 at 09:25

score 1 · Accepted Answer · answered Aug 06 '18 at 09:33

One solution to combine two Macros would be just to type everything from the second Macro between the first and last line and paste in where you need its execution in the first code.

The other solution would be to "Call" the second Macro from the first Code by simply typing

Call Macro8

In your example :

Sub foo()

Dim ws As Worksheet: Set ws = Sheets("inbd")
Dim wsDestination As Worksheet: Set wsDestination = Sheets("test")

LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("A1:N" & LastRow).AutoFilter Field:=1, Criteria1:=Worksheets("test").Cells(1, 26).Value
    ws.Range("f2:f" & LastRow).SpecialCells(xlCellTypeVisible).Copy Range("C6")
DestinationRow = wsDestination.Cells(wsDestination.Rows.Count, "C").End(xlUp).Row + 1
    wsDestination.Range("C" & DestinationRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False
ws.Range("A1:N" & LastRow).AutoFilter Field:=1


Call Macro8                         ' Or Copy Paste the whole other code here

End Sub

I still strongly advise to follow the links from the comments of Foxfire And Burns And Burns about How to avoid using Select in Excel VBA.

score 0 · Answer 2 · answered Aug 06 '18 at 09:43

0

Application.run ("macro8") <-is what I needed, I appreciate the advice though, I don't really have any knowledge in coding, but I will try to avoid using select if i can.

answered Aug 06 '18 at 09:43

Joe Royson

49
5

How do i combine macro module with the rest of the code

2 Answers2