Problems with paste column vba

Snabelhund

New Member
Joined
Nov 11, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
I have a macrorelated problem in Excel VBA, where I'm trying to copy a range from one workbook (source) and paste it in a sheet (sh1) in column C in another workbook (target) (there Is already some information in column A in the target workbook.)

However my code seems to be working as far as to the paste step. It seems as the macro quits rather than pastes the content and I'm not getting any error messages and when debugging I really cant identify the problem.
The code is placed in the open workbook named target and the data is text based. Also it is possible for me to manually paste the copied range. so it seems that the copied content i stored to the clipboard.
Excuse me for any spelling errors... english is not my first language. Also i´m somewhat new to VBA, so the code a a regeneration off bits found online. So apoligies if it is related to a simple beginners error..
see code below:


VBA Code:
Sub CopyFromWorkbook()
' Define variables'
Dim wb1 As Workbook, wb2 As Workbook
  
' Disable screen updating to reduce screen flicker'
Application.ScreenUpdating = False
  
' Define which workbook is which
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("H:\source.xlsx")
  
' Copy range A1:D1000 from the Data sheet of wb2
wb2.Sheets("sourceData").Range("A1:D7000").Copy
  
'to avoid clipboard prompt'
  
Application.DisplayAlerts = False
  
' Paste the copied data to the column C of the target sheet in wb1'
  
wb1.Sheets("target").Activate
  
'the code runs fine until here'
  
ActiveSheet.Paste Destination:=wb1.Sheets("target").Range("C1")
  
' Close wb2
    wb2.Close
Application.DisplayAlerts = True
  
' Re-enable screen updating
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,282
Office Version
  1. 2013
Platform
  1. Windows
Change

VBA Code:
ActiveSheet.Paste Destination:=wb1.Sheets("target").Range("C1")
to
VBA Code:
  Range("C1").Select
    ActiveSheet.Paste
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,650
Office Version
  1. 365
Platform
  1. Windows
@Snabelhund your code works for me. Is your code in a standard module ?

1636632158259.png
 

Snabelhund

New Member
Joined
Nov 11, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Change

VBA Code:
ActiveSheet.Paste Destination:=wb1.Sheets("target").Range("C1")
to
VBA Code:
  Range("C1").Select
    ActiveSheet.Paste
Cheers Mohadin, the change seems to work well, Thank you. For my understanding, what difference does change do?
Also out of curiosity is it generally good practice to avoid select when coding in vba?
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,282
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

is it generally good practice to avoid select when coding in vba
Correct
Then the method of copy depends
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,650
Office Version
  1. 365
Platform
  1. Windows
Strange, yes the code is in a standard module
If you are happy to run with mohadin’s version just use that.

If you want to experiment you can try
VBA Code:
ActiveSheet.Paste Destination:=Range("C1")
 

Snabelhund

New Member
Joined
Nov 11, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Another question on the same topic is that when i save the code above as a macro in my personal workbook the same bahaviour occurs. Seems that i works as long as it is run from the same workbook as the traget sheet is in. Unfourtunatley i will need to use the code on several workbooks as the target data is based on weekly reports. Is there a way to make the macro work "globally"? Or is the solution to copy paste the macro in the workbook every time i will neeed to run it?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,032
Messages
5,767,745
Members
425,429
Latest member
MMMMMM

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