excel vba macro: copy and paste the specific column to another workbook

Question

i am new in macro and VBA coding.

I have two workbooks. one is report.xls, another one is AT.xlsm.

In report.xls, there has the sheet called "Service".

In AT.xlsm, there has the sheet called "Worksheet".

I want to auto copy the column B,C,F,J,E,D expect first row of report to the column A,C,D,E,F,H of AT. So, i try to record a macro and revise the code, it can copy and paste but it saved to same location.

Would you please tell me how to paste to new row?

Thank you very much.

Code:

Sub Module1()
'Hot key: Ctrl+Shift+G

Windows("report.xls").Activate
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("AT.xlsm").Activate
ActiveSheet.Paste
Windows("report.xls").Activate
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("AT.xlsm").Activate
Range("C5380").Select
ActiveSheet.Paste
Windows("report.xls").Activate
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("AT.xlsm").Activate
Range("D5380").Select
ActiveSheet.Paste
Windows("report.xls").Activate
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("AT.xlsm").Activate
Range("E5380").Select
ActiveSheet.Paste
Windows("report.xls").Activate
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("AT.xlsm").Activate
Range("F5380").Select
ActiveSheet.Paste
Windows("report.xls").Activate
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("AT.xlsm").Activate
Range("H5380").Select
ActiveSheet.Paste
Windows("report.xls").Activate
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("AT.xlsm").Activate
Range("J5380").Select
ActiveSheet.Paste
Range("F5380").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="[S]", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

mooseman · Accepted Answer · 2017-12-07 14:30:27Z

1

You should not be activating and selecting anything to copy over to another worksheet. wss and wsw are explicit references to your worksheets.

Sub Tester546()
Set wb = Workbook("report.xls")
Set wb2 = Workbook("AT.xlsm")
Set wss = wb.Sheets("Service")
Set wsw = wb.Sheets("Worksheet")
wss.Range(wss.Cells(2, 2), wss.Cells(wss.Range("B" & wss.Rows.Count).End(xlUp).Row, 2)).Copy wsw.Cells(1, 1)
wss.Range(wss.Cells(2, 3), wss.Cells(wss.Range("C" & wss.Rows.Count).End(xlUp).Row, 3)).Copy wsw.Cells(5380, 3)
wss.Range(wss.Cells(2, 6), wss.Cells(wss.Range("F" & ws.Rows.Count).End(xlUp).Row, 6)).Copy wsw.Cells(5380, 4)
wss.Range(wss.Cells(2, 10), wss.Cells(wss.Range("J" & wss.Rows.Count).End(xlUp).Row, 10)).Copy wsw.Cells(5380, 5)
wss.Range(wss.Cells(2, 5), wss.Cells(wss.Range("E" & wss.Rows.Count).End(xlUp).Row, 5)).Copy wsw.Cells(5380, 6)
wss.Range(wss.Cells(2, 4), wss.Cells(wss.Range("D" & wss.Rows.Count).End(xlUp).Row, 4)).Copy wsw.Cells(5380, 8)
wsw.Range(wsw.Cells(5380, 6), wsw.Cells(wsw.Range("F" & wsw.Rows.Count).End(xlUp).Row, 6)).Replace What:="[S]", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub

You should be able to replace the 5380 on the destination worksheet (wsw) with

wsW.Range("D" & wss.Rows.Count).End(xlUp).Row +1

Changing the Column (here it's "D", to the columns you need.

edited Dec 7, 2017 at 14:30

answered Dec 6, 2017 at 17:20

mooseman

2,0052 gold badges17 silver badges31 bronze badges

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

3 Comments

user3472143 Over a year ago

Thank you for your reply, may I know that how to change the cells(5380) to the cells in any row of column A?

user3472143 Over a year ago

I don't always want cell 5380. I want to paste my data then go to last row + 1. the cell would change with each copy and paste, what i need to change in the code? Thanks

user3472143 Over a year ago

Thank you very much for your help. I will try it.

user8753746 · Accepted Answer · 2017-12-06 16:48:41Z

0

I will use a portion of your code. VBA code made while you record a macro is not the best option to use, but this time I will use the code in order to don't do things complicated to you.

Original code:

Windows("report.xls").Activate
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("AT.xlsm").Activate
ActiveSheet.Paste

Modify like:

Windows("report.xls").Activate    'This activate the report
Range("B2").Select    'This selects B2 cell
Range(Selection, Selection.End(xlDown)).Select    'This expands the selection down
Selection.Copy    'This copy the selection.
Windows("AT.xlsm").Activate    'This activate the AT file
Range("B2").Select    '**** Here you should modify to select the Paste destination cell
ActiveSheet.Paste    'This Paste the values

Hope it helps.

answered Dec 6, 2017 at 16:48

user8753746

1 Comment

user3472143 Over a year ago

Thank you for your reply and add the comment that make me easier to understand.

Collectives™ on Stack Overflow

excel vba macro: copy and paste the specific column to another workbook

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related