VBA Code for Pasting Data

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
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!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

Forum statistics

Threads
1,136,795
Messages
5,677,783
Members
419,720
Latest member
kurman

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
Top