VBA Copy, Past, Rename, Copy past and runs through each column

Question

I am trying to build a vba code. Some bits are working some not. Working: It is collecting valued cells from Customers workbook and pasting in new sheet in supplier workbook. New sheet is renamed on cell name. Not working: I also want to copy a header which is in Supplier workbook named as template. The last bit i want VBA code run through each column.

Sub Copy_Past_Repeat()

Dim rs As Worksheet
Dim rng As Range
    Windows("Customer.xlsm").Activate
    Set rng = Range("n1:n1000")   'column
    rng.SpecialCells(xlCellTypeConstants).Select   'Selecting only hardcoded data
   Selection.Copy
   Windows("Supplier.xlsm").Activate
   Sheets.Add after:=ActiveSheet
   Range("C2").Select
    ActiveSheet.Paste
     ActiveSheet.Name = ActiveSheet.Range("C2")

  'not working
   ThisWorkbook.Sheets("Template").Range("A1:E1").Select
    Selection.Copy

   ActiveSheet.Paste 'should be pasted in just crated spreadsheet Name=(C2)
   Application.CutCopyMode = False

  End Sub `

Read [this answer](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/10717999#10717999) to learn how to avoid `Select` and `Activate`. That will help you avoid the problem you're having I think. — PeterT, Oct 24 '18 at 14:23

score 0 · Answer 1 · answered Oct 24 '18 at 14:41

It is highly recommended to avoid using .Select, .Activate or similar actions.
See How to avoid using Select in Excel VBA

Also always specify in which worksheet or workbook a range is. Otherwise Excel cannot know it and guesses.

Option Explicit

Sub Copy_Past_Repeat()
    Dim rng As Range
    Set rng = Workbooks("Customer.xlsm").Range("N1:N1000")   'column

    rng.SpecialCells(xlCellTypeConstants).Copy   'Copy only hardcoded data

    Dim NewWs As Worksheet
    With Workbooks("Supplier.xlsm")
        Set NewWs = .Sheets.Add(After:=.ActiveSheet) 'remember the new added sheet in NewWs so we can access it later 
        NewWs.Range("C2").Paste
        NewWs.Name = NewWs.Range("C2")

        ThisWorkbook.Worksheets("Template").Range("A1:E1").Copy

        NewWs.Paste 'should be pasted in just crated spreadsheet Name=(C2)
        'here you should specify where in the sheet to paste
        'NewWs.Range("A1").Paste
    End With

    Application.CutCopyMode = False
End Sub

VBA Copy, Past, Rename, Copy past and runs through each column

1 Answers1