macro shows error when I run it if my data is only two lines vs. multiple

jwoo89

New Member
Joined
Jan 5, 2021
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
HI All -

I am having an issue running my macro. It works when i paste data with multiple lines but when i paste data with only two lines i get an error. It seems when i paste special it stops running. Can someone take a look? I get an error on this line in bold below:

Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

heres the code:

Rich (BB code):
Sub Deliver_Units()


Sheets("Deliver_Units").Select
Range("A1").Select
ActiveCell.Value2 = "DWAC Withdrawal_Deliver_Units_" & Date
Selection.NumberFormat = "yyyymmdd"
Range("A2").Select
ActiveCell.Value2 = "dwac"
Range("A3").Select
ActiveCell.Value2 = "account_id"
Range("B3").Select
ActiveCell.Value2 = "segment"
Range("C3").Select
ActiveCell.Value2 = "instrument.identifier"
Range("D3").Select
ActiveCell.Value2 = "currency"
Range("E3").Select
ActiveCell.Value2 = "instrument.identifier_type"
Range("F3").Select
ActiveCell.Value2 = "instrument.currency"
Range("G3").Select
ActiveCell.Value2 = "instrument.country"
Range("H3").Select
ActiveCell.Value2 = "position"
Range("I3").Select
ActiveCell.Value2 = "balance"

    Sheets("DWAC").Select
    Range("A1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Deliver_Units").Select
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B4").Select
    ActiveCell.Value2 = "margin"
     Range("B4").AutoFill Range("B4:B" & Range("A" & Rows.Count).End(xlUp).Row)
    Sheets("DWAC").Select
    Range("E1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Deliver_Units").Select
    Range("C4").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     Range("D4").Select
    ActiveCell.Value2 = "USD"
     Range("D4").AutoFill Range("D4:D" & Range("A" & Rows.Count).End(xlUp).Row)
      Range("E4").Select
    ActiveCell.Value2 = "cusip"
     Range("E4").AutoFill Range("E4:E" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("F4").Select
    ActiveCell.Value2 = "USD"
     Range("F4").AutoFill Range("F4:F" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("G4").Select
    ActiveCell.Value2 = "USA"
     Range("G4").AutoFill Range("G4:G" & Range("A" & Rows.Count).End(xlUp).Row)
    Sheets("DWAC").Select
    Range("H1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Deliver_Units").Select
    Range("H4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("J4").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]*-1"
    Range("J4").AutoFill Range("J4:J" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("J4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("H4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("I4").Select
    ActiveCell.Value2 = "0"
     Range("I4").AutoFill Range("I4:I" & Range("A" & Rows.Count).End(xlUp).Row)
    
'DTCF
     Range("J4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("H4").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("C4:G4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
Range("C4").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("I4").End(xlDown).Select
Range("I4").AutoFill Range("I4:I" & Range("H" & Rows.Count).End(xlUp).Row)


Range("C4").Select
Selection.End(xlDown).Select
    ActiveCell.Select
    ActiveCell.Offset(-1, -2).Select
    ActiveCell.Value2 = "100002"
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
   
Range("C4").Select
Selection.End(xlDown).Select
    ActiveCell.Select
    ActiveCell.Offset(-1, -1).Select
    ActiveCell.Value2 = "dtcf"
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
   
    ActiveCell.Select
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
   
    Sheets("INX").Select
   
MsgBox "Withdrawal/Deliver Units Done"

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
HI jwoo
I got as far as I could with cleaning up your code but got stuck wondering why you over write what you already did with the same thing but down a cell?

VBA Code:
Sub Deliver_Units()


With ThisWorkbook.Sheets("Deliver_Units")
    .Range("A1").Value2 = "DWAC Withdrawal_Deliver_Units_" & Date
    .Range("A1").NumberFormat = "yyyymmdd"
    .Range("A2").Value2 = "dwac"
    .Range("A3").Value2 = "account_id"
    .Range("B3").Value2 = "segment"
    .Range("B4").Value2 = "margin"
    .Range("B4").AutoFill .Range("B4:B" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("C3").Value2 = "instrument.identifier"
    .Range("D3").Value2 = "currency"
    .Range("D4").Value2 = "USD"
    .Range("D4").AutoFill .Range("D4:D" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("E3").Value2 = "instrument.identifier_type"
    .Range("E4").Value2 = "cusip"
    .Range("E4").AutoFill .Range("E4:E" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("F3").Value2 = "instrument.currency"
    .Range("F4").Value2 = "USD"
    .Range("F4").AutoFill .Range("F4:F" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("G3").Value2 = "instrument.country"
    .Range("G4").Value2 = "USA"
    .Range("G4").AutoFill .Range("G4:G" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("H3").Value2 = "position"
    .Range("J4").FormulaR1C1 = "=RC[-2]*-1"
    .Range("J4").AutoFill .Range("J4:J" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("H4").FormulaR1C1 = "=RC[-2]*-1"
    .Range("H4").AutoFill .Range("H4:H" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("I4").Value2 = "0"
    .Range("I4").AutoFill .Range("I4:I" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("I3").Value2 = "balance"
End With

With ThisWorkbook.Sheets("DWAC")
    .Range(.Range("A2"), .Range("A2")).End(xlDown).Copy: ThisWorkbook.Sheets("Deliver_Units").Range("A4").PasteSpecial Paste:=xlPasteValues
    .Range(.Range("E2"), .Range("E2")).End(xlDown).Copy: ThisWorkbook.Sheets("Deliver_Units").Range("C4").PasteSpecial Paste:=xlPasteValues
    .Range(.Range("H2"), .Range("H2")).End(xlDown).Copy: ThisWorkbook.Sheets("Deliver_Units").Range("H4").PasteSpecial Paste:=xlPasteValues
    .Range(.Range("J2"), .Range("J2")).End(xlDown).Copy: ThisWorkbook.Sheets("Deliver_Units").Range("H4").PasteSpecial Paste:=xlPasteValues

   
'DTCF

Range("C4:G4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
Range("C4").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("I4").End(xlDown).Select
Range("I4").AutoFill Range("I4:I" & Range("H" & Rows.Count).End(xlUp).Row)


Range("C4").Select
Selection.End(xlDown).Select
    ActiveCell.Select
    ActiveCell.Offset(-1, -2).Select
    ActiveCell.Value2 = "100002"
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
   
Range("C4").Select
Selection.End(xlDown).Select
    ActiveCell.Select
    ActiveCell.Offset(-1, -1).Select
    ActiveCell.Value2 = "dtcf"
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
   
    ActiveCell.Select
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
   
    Sheets("INX").Select
   
MsgBox "Withdrawal/Deliver Units Done"

End Sub
 
Upvote 0
HI All -

I am having an issue running my macro. It works when i paste data with multiple lines but when i paste data with only two lines i get an error. It seems when i paste special it stops running. Can someone take a look? I get an error on this line in bold below:

Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

heres the code:
VBA Code:
Sub Deliver_Units()


Sheets("Deliver_Units").Select
Range("A1").Select
ActiveCell.Value2 = "DWAC Withdrawal_Deliver_Units_" & Date
Selection.NumberFormat = "yyyymmdd"
Range("A2").Select
ActiveCell.Value2 = "dwac"
Range("A3").Select
ActiveCell.Value2 = "account_id"
Range("B3").Select
ActiveCell.Value2 = "segment"
Range("C3").Select
ActiveCell.Value2 = "instrument.identifier"
Range("D3").Select
ActiveCell.Value2 = "currency"
Range("E3").Select
ActiveCell.Value2 = "instrument.identifier_type"
Range("F3").Select
ActiveCell.Value2 = "instrument.currency"
Range("G3").Select
ActiveCell.Value2 = "instrument.country"
Range("H3").Select
ActiveCell.Value2 = "position"
Range("I3").Select
ActiveCell.Value2 = "balance"

    Sheets("DWAC").Select
    Range("A1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Deliver_Units").Select
    [B]Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False[/B]
    Range("B4").Select
    ActiveCell.Value2 = "margin"
     Range("B4").AutoFill Range("B4:B" & Range("A" & Rows.Count).End(xlUp).Row)
    Sheets("DWAC").Select
    Range("E1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Deliver_Units").Select
    Range("C4").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     Range("D4").Select
    ActiveCell.Value2 = "USD"
     Range("D4").AutoFill Range("D4:D" & Range("A" & Rows.Count).End(xlUp).Row)
      Range("E4").Select
    ActiveCell.Value2 = "cusip"
     Range("E4").AutoFill Range("E4:E" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("F4").Select
    ActiveCell.Value2 = "USD"
     Range("F4").AutoFill Range("F4:F" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("G4").Select
    ActiveCell.Value2 = "USA"
     Range("G4").AutoFill Range("G4:G" & Range("A" & Rows.Count).End(xlUp).Row)
    Sheets("DWAC").Select
    Range("H1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Deliver_Units").Select
    Range("H4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("J4").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]*-1"
    Range("J4").AutoFill Range("J4:J" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("J4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("H4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("I4").Select
    ActiveCell.Value2 = "0"
     Range("I4").AutoFill Range("I4:I" & Range("A" & Rows.Count).End(xlUp).Row)
  
'DTCF
     Range("J4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("H4").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("C4:G4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
Range("C4").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("I4").End(xlDown).Select
Range("I4").AutoFill Range("I4:I" & Range("H" & Rows.Count).End(xlUp).Row)


Range("C4").Select
Selection.End(xlDown).Select
    ActiveCell.Select
    ActiveCell.Offset(-1, -2).Select
    ActiveCell.Value2 = "100002"
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
 
Range("C4").Select
Selection.End(xlDown).Select
    ActiveCell.Select
    ActiveCell.Offset(-1, -1).Select
    ActiveCell.Value2 = "dtcf"
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
 
    ActiveCell.Select
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
 
    Sheets("INX").Select
 
MsgBox "Withdrawal/Deliver Units Done"

End Sub

HI jwoo
I got as far as I could with cleaning up your code but got stuck wondering why you over write what you already did with the same thing but down a cell?

VBA Code:
Sub Deliver_Units()


With ThisWorkbook.Sheets("Deliver_Units")
    .Range("A1").Value2 = "DWAC Withdrawal_Deliver_Units_" & Date
    .Range("A1").NumberFormat = "yyyymmdd"
    .Range("A2").Value2 = "dwac"
    .Range("A3").Value2 = "account_id"
    .Range("B3").Value2 = "segment"
    .Range("B4").Value2 = "margin"
    .Range("B4").AutoFill .Range("B4:B" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("C3").Value2 = "instrument.identifier"
    .Range("D3").Value2 = "currency"
    .Range("D4").Value2 = "USD"
    .Range("D4").AutoFill .Range("D4:D" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("E3").Value2 = "instrument.identifier_type"
    .Range("E4").Value2 = "cusip"
    .Range("E4").AutoFill .Range("E4:E" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("F3").Value2 = "instrument.currency"
    .Range("F4").Value2 = "USD"
    .Range("F4").AutoFill .Range("F4:F" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("G3").Value2 = "instrument.country"
    .Range("G4").Value2 = "USA"
    .Range("G4").AutoFill .Range("G4:G" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("H3").Value2 = "position"
    .Range("J4").FormulaR1C1 = "=RC[-2]*-1"
    .Range("J4").AutoFill .Range("J4:J" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("H4").FormulaR1C1 = "=RC[-2]*-1"
    .Range("H4").AutoFill .Range("H4:H" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("I4").Value2 = "0"
    .Range("I4").AutoFill .Range("I4:I" & .Range("A" & Rows.Count).End(xlUp).Row)
    .Range("I3").Value2 = "balance"
End With

With ThisWorkbook.Sheets("DWAC")
    .Range(.Range("A2"), .Range("A2")).End(xlDown).Copy: ThisWorkbook.Sheets("Deliver_Units").Range("A4").PasteSpecial Paste:=xlPasteValues
    .Range(.Range("E2"), .Range("E2")).End(xlDown).Copy: ThisWorkbook.Sheets("Deliver_Units").Range("C4").PasteSpecial Paste:=xlPasteValues
    .Range(.Range("H2"), .Range("H2")).End(xlDown).Copy: ThisWorkbook.Sheets("Deliver_Units").Range("H4").PasteSpecial Paste:=xlPasteValues
    .Range(.Range("J2"), .Range("J2")).End(xlDown).Copy: ThisWorkbook.Sheets("Deliver_Units").Range("H4").PasteSpecial Paste:=xlPasteValues

  
'DTCF

Range("C4:G4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
Range("C4").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("I4").End(xlDown).Select
Range("I4").AutoFill Range("I4:I" & Range("H" & Rows.Count).End(xlUp).Row)


Range("C4").Select
Selection.End(xlDown).Select
    ActiveCell.Select
    ActiveCell.Offset(-1, -2).Select
    ActiveCell.Value2 = "100002"
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
  
Range("C4").Select
Selection.End(xlDown).Select
    ActiveCell.Select
    ActiveCell.Offset(-1, -1).Select
    ActiveCell.Value2 = "dtcf"
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
  
    ActiveCell.Select
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
  
    Sheets("INX").Select
  
MsgBox "Withdrawal/Deliver Units Done"

End Sub
Thanks so much for your help! In regards to rewriting the same macro i had to change the segment input to dtcf instead of inventory and i also need to take the position from what was posted above and copy and make it negative. Do you think you can help? Here is a sample set of data sometimes it is one line or sometimes 3 or ten lines.
Account IDAccount NameSymbolUnit Sep QtyUnitCommonWTSDeliver in UnitsReceive in CommonReceive in Warrants
100001​
XYZQQQ
100​
XYZ123XYZ321XYZ12345
1000​
1000​
750​
100002​
XYZAAA
200​
ABC123ABC321ABC12345
1000​
1000​
750​
100003​
XYZBBB
300​
DEF123DEF321DEF12345
1000​
1000​
750​
 
Upvote 0
In the original code, when you paste data with only 2 lines it breaks because you are telling the code to highlight the top row and go from the second down row(which in this case is the bottom row of data) and go to the bottom which it will go to the bottom of the sheet so remove the section in bold below

Sheets("DWAC").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

But please have a look at how I was cleaning up the code and try to do that with all of it. not only will it run faster but much more stable too
Try to never use select
 
Upvote 0
I should explain why this is a problem
When you copy from A1 to the bottom and then try to paste it in a lower row excel doesn't let you do it because it would have to paste the last few rows below the lowest row in excel so it errors
Test it, get a blank sheet highlight the entire A column and Ctrl + c then select B5 and Ctrl + v. error
 
Upvote 0
I should explain why this is a problem
When you copy from A1 to the bottom and then try to paste it in a lower row excel doesn't let you do it because it would have to paste the last few rows below the lowest row in excel so it errors
Test it, get a blank sheet highlight the entire A column and Ctrl + c then select B5 and Ctrl + v. error
Thanks so much for the explanation! and your vba worked! i really appreciate your help!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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