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

J

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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
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
jwoo89 said:
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
Click to expand...

EFANYoutube said:
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
Click to expand...
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
www.excelcampus.com

How to Avoid the Select Method in VBA & Why - Excel Campus

The Select method in VBA can be just as time consuming for Excel, as it is for you to get up off the couch to change the channel.
www.excelcampus.com www.excelcampus.com
 
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
EFANYoutube said:
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
Click to expand...
Thanks so much for the explanation! and your vba worked! i really appreciate your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
205
MikeVol
MikeVol
E
  • Solved
macro for copying values to the next blank cell
Replies
5
Views
451
Micron
M
R
  • Solved
Retype Macro to run properly
2
Replies
11
Views
137
Alex Blakenburg
A
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
386
willow1985
willow1985
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
439
myall_blues
myall_blues

Forum statistics

Threads
1,224,560
Messages
6,179,520
Members
452,923
Latest member
JackiG

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