1

Title isn't the best so here a an overview.

I'm using VBA to copy select columns from one workbook to another, as what will be part of a larger automated program.

On the Workbook I am copying from, there are different sheets containing a "Stock Number" column. When pasting into my other workbook, I am trying to get these columns to merge into 1 single column (pasting below the last entry from the first sheet and so on).

Here is my current code:

Sub import_adam_article()

Windows( _
    "copyfrom.xlsx" _
    ).Activate
Columns("F:G").Select
Selection.Copy
Windows("pasteinto.xlsx").Activate
Columns("A:A").Select
ActiveSheet.Paste

Windows( _
    "copyfrom.xlsx" _
    ).Activate
Columns("N:N").Select
Application.CutCopyMode = False
Selection.Copy
Windows("pasteinto.xlsx").Activate
Columns("C:C").Select
ActiveSheet.Paste

Rows("1:1").Select
Selection.Delete Shift:=xlUp

NextRow = Range("A1").End(xlDown).Row + 1

Windows( _
    "copyfrom.xlsx" _
    ).Activate
Columns("F:G").Select
Selection.Copy
Windows("pasteinto.xlsx").Activate
Range("A" & (NextRow)).Select
ActiveSheet.Paste


[A:C].Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
End With  
End Sub

The difficulty is that the amount of Stock Numbers will change every new file that comes through, so it needs to be able to adjust to varying amounts.

I can't seem to find a way to make it work and I've tried searching for answers elsewhere.

EDIT: The current issue with the code that it is selecting the next empty row to paste into, but only that cell, not a variable length down as required by the copyfrom column.

BaronMartin
  • 51
  • 1
  • 2
  • 7
  • 3
    Your first priority should be [getting rid of Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Nacorid Aug 06 '19 at 13:15
  • 1
    I am aware of this, however this is not my question. – BaronMartin Aug 06 '19 at 13:17
  • You need to identify your worksheet object. Also, when you paste you have to recalculate your `NextRow` – GMalc Aug 06 '19 at 13:19
  • @GMalc Thank you, please could you explain what you mean by this? Sorry, just not experienced in VBA. Thank you. – BaronMartin Aug 06 '19 at 13:21
  • 1
    Possible duplicate of [VBA Copying data from one workbook to another using lastrow ranges](https://stackoverflow.com/questions/56971952/vba-copying-data-from-one-workbook-to-another-using-lastrow-ranges) – Damian Aug 06 '19 at 13:34
  • @Damian thank you though It isn't clear that that is the same issue exactly. I'm struggling to understand the linked question. Does this copy a column, then paste that entire column into a new column underneath existing data (of which that row destination will change each time) – BaronMartin Aug 06 '19 at 13:44
  • 2
    Your paste should be written something like this `Workbooks("pasteinto").Sheets("Sheet Name").Cells(Rows.Count, 1).End(xlUp).Offset(1).Paste` which will reset the lastrow for each paste. BUT that won't fix all your problems because you will need to `LOOP` thru each worksheet in `Workbooks("Copyfrom")` and copy each column range, as your question states that you want to "get these columns to merge into 1 single column". Per @Nacorid `Select`, `Windows`, and `Activate` are poor coding practices, and should be avoided. – GMalc Aug 06 '19 at 14:02
  • It should be your question because you could probably reduce that code to about 3 lines! – SJR Aug 06 '19 at 14:21
  • While I appreciate the enthusiasm about cleaning the code, as stated, I know full well it needs changing. The code is from a macro recording to act as a placeholder to give context to a problem I did not know the answer to. Thank you to @GMalc who has given the best information so far and actually trying to answer the question. – BaronMartin Aug 06 '19 at 14:49

0 Answers0