Excel VBA - Incorporate IF "" on an Application.Run statement? Or use Loop?

JDescant

New Member
Joined
Jul 24, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Originally, I wanted to copy / paste some data from one sheet to another, so I created the below macro to do so. I have macros to call out each specific row in order to keep the code simple (or so I thought). I then created another macro to "export" the data by using the application.run command for all the macros. However, when I don't have data in a row, the fill.down command on the paste sheet still wants to execute. I know there's probably a way to incorporate an IF Blank statement. Or should I have just used a loop to begin with? I was trying to steer away from them because I don't understand the code and how it works. Below are my current macros

This macro is is duplicated for each specific row

VBA Code:
Sub Row_11()
'
'Row_11 Macro
'
'Copy and transpose treatment data
Range("F9:I9,F11:I11").Select
Range("F11").Activate
Selection.Copy
Sheets("Billing Form").Select
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
'Then copy and paste Location & Wellmaster based on # of transposed cells above
Sheets("GEP 1").Select
Range("A11,C11:D11").Select
Selection.Copy
Sheets("Billing Form").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
If Range("A10") = "" Then
Range("A9:C" & LastRow).FillDown
Sheets("GEP 1").Select
Else
Range(Cells(Rows.Count, 1).End(xlUp), "C" & LastRow).FillDown
Sheets("GEP 1").Select
End If

End Sub

Then I use this to "export" the data.

VBA Code:
Sub Export_GEP_1()
'
' Export Macro
'

'
Application.Run "Module3.Row_10"
Application.Run "Module3.Row_11"
Application.Run "Module3.Row_12"
Application.Run "Module3.Row_13"
Application.Run "Module3.Row_14"
Application.Run "Module3.Row_15"
Application.Run "Module3.Row_16"
Application.Run "Module3.Row_17"
Application.Run "Module3.Row_18"
Application.Run "Module3.Row_19"
Application.Run "Module3.Row_20"
Application.Run "Module3.Row_21"
Application.Run "Module3.Row_22"
Application.Run "Module3.Row_23"
Application.Run "Module3.Row_24"
End Sub
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
678
Office Version
  1. 365
Platform
  1. Windows
Rich (BB code):
Range("F9:I9,F11:I11").Select

Do you copy F9:I9 throughout all of the codes? Or would Row 10 code be...

Rich (BB code):
Range("F8:I8,F10:I10").Select
 

JDescant

New Member
Joined
Jul 24, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
F9:I9 is copied through all. Those are my headers
 

JDescant

New Member
Joined
Jul 24, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Here's a couple screen shots

Copy area
1596815991764.png


Paste area
1596816059358.png
 

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
678
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

why do you copy those everytime? arent those columns going to stay the same and only be at the top? or do you paste it so you get a separation of each paste?
 

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
678
Office Version
  1. 365
Platform
  1. Windows
Can you share the code for Row 10 or Row 11
 

JDescant

New Member
Joined
Jul 24, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I'm copying and transposing onto another tab.

Here's the code for Row 10. Basically the same thing.

VBA Code:
Sub Row_10()
'
'Row_10 Macro
'
'Copy and transpose treatment data
    Range("F9:I9,F10:I10").Select
    Range("F10").Activate
    Selection.Copy
    Sheets("Billing Form").Select
    Range("D" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
        Application.CutCopyMode = False
'Then copy and paste Location & Wellmaster based on # of transposed cells above
    Sheets("GEP 1").Select
    Range("A10,C10:D10").Select
    Selection.Copy
    Sheets("Billing Form").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Dim LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
    If Range("A10") = "" Then
    Range("A9:C" & LastRow).FillDown
    Sheets("GEP 1").Select
    Else
    Range(Cells(Rows.Count, 1).End(xlUp), "C" & LastRow).FillDown
    Sheets("GEP 1").Select
    End If
End Sub
 

JDescant

New Member
Joined
Jul 24, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Just wanted to follow up on this

I tried to incorporate something like this at the beginning but I think the second If statement is throwing it off and not allowing the macro to run.

VBA Code:
If ActiveSheet.Range("F10").Value = "" Then Exit Sub
Else 'run macro'
 

Watch MrExcel Video

Forum statistics

Threads
1,122,980
Messages
5,599,160
Members
414,295
Latest member
Dolenhil

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