VBA Code for Pasting Data

PaigeWarner

New Member
Joined
May 27, 2020
Messages
48
Office Version
  1. 365
Platform
  1. MacOS
I am trying to figure out how to adjust the below code to work. I used the record macro feature with both my sheets open but when I run it, it copies what is in the current active sheet instead of the one I want it to copy from.

I am trying to copy a row of data from one excel file called "Batch Copy-Paste" in a tab called "Sheet1", fields A1:A5001. I then want the macro to paste in my current active excel files I am working on, in column AE.

Sub CopyAndPastMacro()
'
' CopyAndPastMacro Macro
'

'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
Range("AE1").Select
ActiveSheet.Paste
End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
First of all can you please use code tags when posting code as it is easier to copy and read.
Maybe try the code below

VBA Code:
Sub CopyAndPastMacro()
    '
    ' CopyAndPastMacro Macro
    '

    '
    With Workbooks("Batch Copy-Paste").Sheets("Sheet1")
        .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Range("AE1")
    End Sub

End Sub
 
Upvote 0
Thanks for the reminder to use code tags Mark and thank you for your help! Unfortunately, I tried this code and it didn't do anything for me. Should I have added it to the code I already had? Just want to make sure I didn't do anything wrong when trying to execute it. Thanks again for your help!
 
Upvote 0
Well the code should have errored (not done nothing) as I made a typo. Try...

VBA Code:
Sub CopyAndPastMacro()
    '
    ' CopyAndPastMacro Macro
    '

    '
    With Workbooks("Batch Copy-Paste").Sheets("Sheet1")
        .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Range("AE1")
    End With
End Sub
 
Upvote 0
Running it this time, I did get an error and when I hit debug this is what I see:
Screen Shot 2020-06-30 at 1.32.25 PM.png
 
Upvote 0
If it is a Runtime Error 9 then it is a naming error (as it can't find either the workbook or the sheet).
 
Last edited:
Upvote 0
The file name and sheet name match exactly but it looks like it is a Runtime Error 9, according to this pop-up:
Screen Shot 2020-06-30 at 2.02.07 PM.png
 
Upvote 0
I should have said 9. Check the spelling and spacing in the workbook name is exactly correct.

What you can also try if you are certain it is correct is adding the file extension i.e. Workbooks("Batch Copy-Paste.xlsm"), making sure that you use the correct extension.

Obviously the workbook must be open
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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