Create a macro that copies specific data in columns and pastes the data into different columns in a different workbook

Question

I am trying to create a macro which selects specific columns in one workbook and then in another workbook according to the column the data is pasted, as the columns are different in both workbooks.

I am able to select in Workbook 1; column C and in Workbook 2 column Z3 and paste the values; but for this I have to highlight all the values in Workbook 1: Column C and then run the macro.

I don't know how to create a macro which does this on click- automatically and also for multiple columns. Below I have included the code that works when the values are selected,

Range("C47:C581").Select
Selection.Copy
Windows("Workbook2.xls").Activate
Range("Z3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("Workbook1.xlsm").Activate

I am not able to include images so i will explain below using examples. Below is what Workbook 1 Sheet 1.

Column A    Column B    Column C    Column 
1             1           11          5555
2             2           22          4444
2             3           33          3333

I want to create a macro that finds the number of cells in Column A (not including the heading) and pastes them into Workbook 2 Column C . The code above in my description only works for a specified range; i have tried the NumberOfCells but i am not sure why this doesn't work

Change Selection.Copy to workbooks("Workbook1.xlsm").Sheets("Sheet1").Columns(3).copy — findwindow
– findwindow, Commented Sep 17, 2015 at 16:51
Thank you, I just tried this; it appears to select the entire column and doesn't paste any values. I also receive an error message to say that the copy and paste area are not the same..do you know why this is? — S J
– S J, Commented Sep 17, 2015 at 18:26
I'm new to VB so i'm sort of running and recording macros and then changing the code...Yes it works if i select the same number of cells. I have another macro which finds the range but the range can change so i was trying to work out how it can find, select and paste the same values — S J
– S J, Commented Sep 17, 2015 at 18:36
Yes, i mean the range, the below code works but this range can change; Range("C47:C581").Select Selection.Copy Windows("Sheet2.xls").Activate Range("Z3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Windows("Sheet1.xlsm").Activate — S J
– S J, Commented Sep 17, 2015 at 18:41
Do the columns that you want copied in both workbooks share same heading? Also, macros are cool but copy pasting is easy. Why macro? — findwindow
– findwindow, Commented Sep 17, 2015 at 18:57

findwindow · Accepted Answer · 2015-09-18 18:25:35Z

2

Your code and your explanation don't match so this is a guess to what you want. Backup your files first.

This takes column A from workbook1, sheet1 (excluding heading) and copies it to workbook2, sheet1, column C starting at cell C2.

Sub SO()

Dim lastRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Workbooks("workbook1.xlsm").WorkSheets("Sheet1")
Set ws2 = Workbooks("workbook2.xlsm").WorkSheets("Sheet1")

lastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row

ws1.Range(Cells(2, 1), Cells(lastRow, 1)).Copy

ws2.Range("C2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End Sub

answered Sep 18, 2015 at 18:25

findwindow

3,2081 gold badge15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create a macro that copies specific data in columns and pastes the data into different columns in a different workbook

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related