VBA does not continue to copy after blank cell

S

SantanaKRE8s

Board Regular
Joined
Jul 11, 2023
Messages
114
Office Version
  1. 365
Platform
  1. Windows
Hello I have a VBA code to copy columns from one report and paste onto my file, there is two columns that have a blank cell and after that cell nothing else is opied for those columns. How can I get it to continue to copy the rest of the cells in the column.

Sub GT_DTA()
'
' GT_DTA Macro
'

'
Windows("BACKLOG.xlsm").Activate
Sheets("SPX BACKLOG").Select
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("L2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("M2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("N2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("O2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("P2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll ToRight:=-1
Sheets("TTI").Select
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("AI2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("T2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("V2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("W2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("X2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("AD2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("L2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("WAS_CAS_" & Format(Date, "mm-dd-yyyy") & ".xlsx").Activate
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll ToRight:=-1

End Sub




1696867980102.png
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Re: "there is two columns that have a blank cell"
In the picture it shows three columns starting at Row 41.
Is it different columns then these three?
Re: " a blank cell"
That means one blank cell but the picture shows multiple blank cells.
Is that blank cell located somewhere else?
You show us 275 lines of code and I assume you want people to wade through that.
It will be a lot better to explain in detail what you want to achieve.
Be concise and to the point.
 
Upvote 0
I want the code to copy the complete column even if it has blanks. right now when it runs into a blank cell its stops copyin the rest of the column. But there is non blnk cells after the blank cells.
 
Upvote 0
I copied only a piece of the Code, In red below it says " SkipBlanks " Im not sure if this is whats causing the problem.

Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 
Upvote 0
Selecting is very seldom required and it slows down running the macro.
xlDown is notorious for creating the problems you describe.

To copy all data in a column to the last used cell you should use
Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
or
Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy

If all the columns are the same lengths, set the last cell first and use that
LastCell = Cells.Find("*", ,xlValues , , xlByRows, xlPrevious).Row
Range("A2:A" & LastCell).Copy
Range("B2:B" & LastCell).Copy

This is just to give you an idea. There is better ways.
Too much to go through at the moment though.
 
Upvote 1
Solution
Try this with copies of your originals.

Change the wb1, wb2, sh1, sh2 to meaningful names
Change references like sheet names as required.
Following should replace your first 151 lines of code.
Code: 
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr As Long
Set wb1 = Workbooks("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv")
Set wb2 = Workbooks("BACKLOG.xlsm")
Set sh1 = wb1.Worksheets("Sheet1")
Set sh2 = wb2.Worksheets("Sheet1")
lr = sh1.Cells.Find("*", ,xlValues , , xlByRows, xlPrevious).Row
sh2.Range("A2:B" & lr).Value = sh1.Range("A2:B" & lr).Value
sh2.Range("D2:P" & lr).Value = sh1.Range("C2:O" & lr).Value

You can insert the other workbooks/worksheets names and use the same logic for that.
Your 275 lines of code should be reduced to somewhere around 20 lines and a lot easier to maintain/change when needed.
 
Upvote 0
How can I implement this option " Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Copy " into mine. Can you show me how much to remvoe of my code to then add this code you gave me.

Windows("SpaceX_Open_Order_Report_" & Format(Date, "m-d-yyyy") & ".csv").Activate
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
Windows("BACKLOG.xlsm").Activate
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
jolivanes said:
Selecting is very seldom required and it slows down running the macro.
xlDown is notorious for creating the problems you describe.

To copy all data in a column to the last used cell you should use
Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
or
Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy

If all the columns are the same lengths, set the last cell first and use that
LastCell = Cells.Find("*", ,xlValues , , xlByRows, xlPrevious).Row
Range("A2:A" & LastCell).Copy
Range("B2:B" & LastCell).Copy

This is just to give you an idea. There is better ways.
Too much to go through at the moment though.
Click to expand...
 
Last edited:
Upvote 0
jolivanes said:
Selecting is very seldom required and it slows down running the macro.
xlDown is notorious for creating the problems you describe.

To copy all data in a column to the last used cell you should use
Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
or
Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy

If all the columns are the same lengths, set the last cell first and use that
LastCell = Cells.Find("*", ,xlValues , , xlByRows, xlPrevious).Row
Range("A2:A" & LastCell).Copy
Range("B2:B" & LastCell).Copy

This is just to give you an idea. There is better ways.
Too much to go through at the moment though.
Click to expand...
I used this option " Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Copy "and it worked for one column so I will use it for the rest of the columns. Thank you.
 
Upvote 0
Keep on playing with it for a while and try to digest what it does.
Copy and Paste can be replaced as I showed you in Post #6 (.Value = .Value scenario)
If you can't get it going, come back here and let us know.
In this part "Cells(Rows.Count, 1)" the 1 means the first Column which is Column A.
If you do the D Column, you would go like this
Code: 
Range("D2:D" & Cells(Rows.Count, 4).End(xlUp).Row).Copy
 
Upvote 1
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
168
shknbk2
S
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
348
Joe4
Joe4
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
107
willow1985
willow1985
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
189
DanteAmor
DanteAmor
S
  • Solved
Macro Not running
2 3
Replies
28
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,215,076
Messages
6,122,983
Members
449,092
Latest member
Mr Hughes

Share this page

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