VBA Copy and Paste Macro refusing to copy two columns

sanantonio

Board Regular
Joined
Oct 26, 2021
Messages
124
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Written a simple copy and paste macro. It transfers data from a table to a specific area. There's several columns to copy and paste and they all function apart from two.

100% the column names match!

Code is this:
VBA Code:
Sub testing()

    'Copies Order Area
    Worksheets("PCM").Select
    Range("Memos[Order Area]").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("B8").Select
    ActiveSheet.Paste
    
    'Copies Price Change Set
    Worksheets("PCM").Select
    Range("Memos[Price Change Set]").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("C8").Select
    ActiveSheet.Paste
    
    'Copies Trend
    Worksheets("PCM").Select
    Range("Memos[Trend]").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("D8").Select
    ActiveSheet.Paste
    
    'Copies Item Nr
    Worksheets("PCM").Select
    Range("Memos[Article]").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("E8").Select
    ActiveSheet.Paste

    'Copies Item Desc
    Worksheets("PCM").Select
    Range("Memos[Item Description]").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("F8").Select
    ActiveSheet.Paste
    
    'Copies store
    Worksheets("PCM").Select
    Range("Memos[Store]").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("G8").Select
    ActiveSheet.Paste

    'Copies Price Zone
    Worksheets("PCM").Select
    Range("Memos[Zone]").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("H8").Select
    ActiveSheet.Paste

    'Copies Old $
    Worksheets("PCM").Select
    Range("Memos[Price Old").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("I8").Select
    ActiveSheet.Paste

    'Copies New $
    Worksheets("PCM").Select
    Range("Memos[Price New").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("J8").Select
    ActiveSheet.Paste
    
    'Copies Valid From
    Worksheets("PCM").Select
    Range("Memos[Valid From]").Select
    Selection.Copy
    Worksheets("PPC").Select
    Range("K8").Select
    ActiveSheet.Paste

End Sub

It's the "Copies Old $" and "Copies New $" that it struggling with. I've tried renaming the columns, I've tried having the data copy to different columns within the table. But it's just debugs on it everytime. This is the error I get.

1685538939289.png


These are the columns it's trying to copy:
1685538986063.png

They're formatted as "General".

Any ideas from y'all?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi @sanantonio .
I hope you are well.

Missing closing bracket:
Range("Memos[Price Old]").Select

Range("Memos[Price New]").Select


----------------------​

You can simplify your code like this:
VBA Code:
Sub testing_v1()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Set sh1 = Sheets("PCM")     'source sheet
  Set sh2 = Sheets("PPC")     'destination sheet
    
  sh1.Range("Memos[Order Area]").Copy sh2.Range("B8")
  sh1.Range("Memos[Price Change Set]").Copy sh2.Range("C8")
  sh1.Range("Memos[Trend]").Copy sh2.Range("D8")
  sh1.Range("Memos[Article]").Copy sh2.Range("E8")
  sh1.Range("Memos[Item Description]").Copy sh2.Range("F8")
  sh1.Range("Memos[Store]").Copy sh2.Range("G8")
  sh1.Range("Memos[Zone]").Copy sh2.Range("H8")
  sh1.Range("Memos[Price Old]").Copy sh2.Range("I8")
  sh1.Range("Memos[Price New]").Copy sh2.Range("J8")
  sh1.Range("Memos[Valid From]").Copy sh2.Range("K8")
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Last edited:
Upvote 0
Solution
Hi @sanantonio .
I hope you are well.

Just another way to simplify:

VBA Code:
Sub testing_v2()
  Dim arr As Variant, itm As Variant
  Dim j As Long
  
  arr = Array("Order Area", "Price Change Set", "Trend", "Article", "Item Description", "Store", "Zone", "Price Old", "Price New", "Valid From")
  j = 2
  For Each itm In arr
    Sheets("PCM").Range("Memos[" & itm & "]").Copy Sheets("PPC").Cells(8, j)
    j = j + 1
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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