VBA does not continue to copy after blank cell

S

SantanaKRE8s

Board Regular
Joined
Jul 11, 2023
Messages
131
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
280
willow1985
willow1985
E
  • Solved
macro for copying values to the next blank cell
Replies
5
Views
415
Micron
M
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
429
myall_blues
myall_blues
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
441
CapRavOr
C
J
  • Question Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
412
shknbk2
S

Forum statistics

Threads
1,223,888
Messages
6,175,213
Members
452,618
Latest member
Tam84

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