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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
Here's a couple screen shots

Copy area
1596815991764.png


Paste area
1596816059358.png
 
Upvote 0
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?
 
Upvote 0
Can you share the code for Row 10 or Row 11
 
Upvote 0
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
 
Upvote 0
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'
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top