VBA Copy and Paste Macro refusing to copy two columns

[sanantonio]

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:

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.

These are the columns it's trying to copy:

They're formatted as "General".

Any ideas from y'all?

 

[DanteAmor]

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:

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
--------------​

 

[DanteAmor]

Hi @sanantonio .
I hope you are well.

Just another way to simplify:

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
--------------​