Macro for copying 3 columns from one worksheet to another worksheet?

K

kishengiggs1

New Member
Joined
Jun 21, 2011
Messages
23
I need some help creating macro for copying 3 columns from sheet1 (workbook1) to workbook2 and 3 columns from sheet2 (workbook1) workbook2 and it should paste from where the data from sheet1 (workbook1) ended and i will have to do this from 3 more different sheets from workbook1.

I will be doing this everyday so i want to make sure it the pasting gets done from where the previous copied data ended last.

Any help would be appreciated.
 
Sort by date Sort by votes
kishengiggs1 said:
Ya i do have workbook1 and sheet1. That error is resolved.

I had some other question:

In the ("sheet1".xls) i dont need the quotation mark right?

Also i am getting object required error on the same line as before.

thanks
Kishen
Click to expand...

You just lost me.

Where is there a ("Sheet1".xls) ??

Is that the actual name of your sheet?

shouldn't it be ("Sheet1")


I have to run right now, if no one else replies, by the time I get back. I'll contact you when I return.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi there,

It works but it does not work correctly. What it does is that it only copies and paste what is in workbook3(sheet2) which is the last one in my order. I can see when it runs, the data from workbook1 and 2 flashes but at last it only copies the one from workbook3(sheet2).

I want it so that it should paste all the data from worksheet1(sheet1 and 2), worksheet2(sheet1 and 2) and worksheet3(sheet 1 and 2) in order according to the columns mentioned.

thanks
Kishen
 
Upvote 0
kishengiggs1 said:
Hi there,

It works but it does not work correctly. What it does is that it only copies and paste what is in workbook3(sheet2) which is the last one in my order. I can see when it runs, the data from workbook1 and 2 flashes but at last it only copies the one from workbook3(sheet2).

I want it so that it should paste all the data from worksheet1(sheet1 and 2), worksheet2(sheet1 and 2) and worksheet3(sheet 1 and 2) in order according to the columns mentioned.

thanks
Kishen
Click to expand...

Can you show me the code that you have so far? It worked when I tested it.
 
Upvote 0
The code is below. I changed some of the workbook names according to what i have.

Code: 
Sub kishen()
Dim lr As Long
Dim lr1 As Long
Dim lr2 As Long
Dim lr2a As Long
Dim lr3 As Long
Dim lr3a As Long
Dim lr4 As Long
lr = Workbooks("kishen1.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr1 = Workbooks("kishen1.xlsx").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Workbooks("kishen2.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr2a = Workbooks("kishen2.xlsx").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr3 = Workbooks("kishen3.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr3a = Workbooks("kishen3.xlsx").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr4 = Workbooks("Book4.xlsm").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
Workbooks("kishen1.xlsx").Sheets("Sheet1").Range("D2:E" & lr).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen1.xlsx").Sheets("Sheet1").Range("AC2:AC" & lr).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen1.xlsx").Sheets("Sheet2").Range("D2:E" & lr1).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen1.xlsx").Sheets("Sheet2").Range("AC2:AC" & lr1).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet1").Range("D2:E" & lr2).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet1").Range("C2:C" & lr2).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet2").Range("D2:E" & lr2a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet2").Range("C2:C" & lr2a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet1").Range("D2:E" & lr3).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet1").Range("C2:C" & lr3).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet2").Range("D2:E" & lr3a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet2").Range("C2:C" & lr3a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)

End Sub
 
Upvote 0
kishengiggs1 said:
The code is below. I changed some of the workbook names according to what i have.

Code: 
Sub kishen()
Dim lr As Long
Dim lr1 As Long
Dim lr2 As Long
Dim lr2a As Long
Dim lr3 As Long
Dim lr3a As Long
Dim lr4 As Long
lr = Workbooks("kishen1.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr1 = Workbooks("kishen1.xlsx").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Workbooks("kishen2.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr2a = Workbooks("kishen2.xlsx").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr3 = Workbooks("kishen3.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr3a = Workbooks("kishen3.xlsx").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr4 = Workbooks("Book4.xlsm").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
Workbooks("kishen1.xlsx").Sheets("Sheet1").Range("D2:E" & lr).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen1.xlsx").Sheets("Sheet1").Range("AC2:AC" & lr).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen1.xlsx").Sheets("Sheet2").Range("D2:E" & lr1).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen1.xlsx").Sheets("Sheet2").Range("AC2:AC" & lr1).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet1").Range("D2:E" & lr2).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet1").Range("C2:C" & lr2).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet2").Range("D2:E" & lr2a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet2").Range("C2:C" & lr2a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet1").Range("D2:E" & lr3).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet1").Range("C2:C" & lr3).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet2").Range("D2:E" & lr3a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet2").Range("C2:C" & lr3a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)

End Sub
Click to expand...

By the looks of the code, everything seems in order. See my PM.
 
Upvote 0
kishengiggs1 said:
The code is below. I changed some of the workbook names according to what i have.

Code: 
Sub kishen()
Dim lr As Long
Dim lr1 As Long
Dim lr2 As Long
Dim lr2a As Long
Dim lr3 As Long
Dim lr3a As Long
Dim lr4 As Long
lr = Workbooks("kishen1.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr1 = Workbooks("kishen1.xlsx").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Workbooks("kishen2.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr2a = Workbooks("kishen2.xlsx").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr3 = Workbooks("kishen3.xlsx").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr3a = Workbooks("kishen3.xlsx").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr4 = Workbooks("Book4.xlsm").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
Workbooks("kishen1.xlsx").Sheets("Sheet1").Range("D2:E" & lr).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen1.xlsx").Sheets("Sheet1").Range("AC2:AC" & lr).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen1.xlsx").Sheets("Sheet2").Range("D2:E" & lr1).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen1.xlsx").Sheets("Sheet2").Range("AC2:AC" & lr1).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet1").Range("D2:E" & lr2).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet1").Range("C2:C" & lr2).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet2").Range("D2:E" & lr2a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen2.xlsx").Sheets("Sheet2").Range("C2:C" & lr2a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet1").Range("D2:E" & lr3).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet1").Range("C2:C" & lr3).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet2").Range("D2:E" & lr3a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("A" & lr4)
Workbooks("kishen3.xlsx").Sheets("Sheet2").Range("C2:C" & lr3a).Copy Workbooks("Book4.xlsm").Sheets("Sheet1").Range("C" & lr4)

End Sub
Click to expand...


I got your file and I am trying too find a solution. Part of the issue has been resolved, but there is still some work to do. However, I can't get back too it until Monday. Is that going to be a problem?
 
Upvote 0
kishengiggs1 said:
Oh no. take your time. You have been a great help to me. Aprreciate it.
Click to expand...


See if these modifications help?

Code: 
Sub kishen1()

Dim lr As Long
Dim lr1 As Long
Dim lr2 As Long
Dim lr2a As Long
Dim lr3 As Long
Dim lr3a As Long

lr = Workbooks("kishen1.xls").Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row
lr1 = Workbooks("kishen1.xls").Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Row
lr2 = Workbooks("kishen2.xls").Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row
lr2a = Workbooks("kishen2.xls").Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Row
lr3 = Workbooks("kishen3.xls").Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row
lr3a = Workbooks("kishen3.xls").Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Row

Workbooks("kishen1.xls").Sheets("Sheet1").Range("D2:E" & lr).Resize(lr).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
Workbooks("kishen1.xls").Sheets("Sheet1").Range("AC2:AC" & lr).Resize(lr).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)(2)
Workbooks("kishen1.xls").Sheets("Sheet2").Range("D2:E" & lr1).Resize(lr1).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
Workbooks("kishen1.xls").Sheets("Sheet2").Range("AC2:AC" & lr1).Resize(lr1).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)(2)
Workbooks("kishen2.xls").Sheets("Sheet1").Range("D2:E" & lr2).Resize(lr2).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
Workbooks("kishen2.xls").Sheets("Sheet1").Range("C2:C" & lr2).Resize(lr2).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)(2)
Workbooks("kishen2.xls").Sheets("Sheet2").Range("D2:E" & lr2a).Resize(lr2a).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
Workbooks("kishen2.xls").Sheets("Sheet2").Range("C2:C" & lr2a).Resize(lr2a).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)(2)
Workbooks("kishen3.xls").Sheets("Sheet1").Range("D2:E" & lr3).Resize(lr3).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
Workbooks("kishen3.xls").Sheets("Sheet1").Range("C2:C" & lr3).Resize(lr3).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)(2)
Workbooks("kishen3.xls").Sheets("Sheet2").Range("D2:E" & lr3a).Resize(lr3a).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
Workbooks("kishen3.xls").Sheets("Sheet2").Range("C2:C" & lr3a).Resize(lr3a).Copy Workbooks("Book4.xls").Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)(2)

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
combining data from multiple workbooks with the same sheet name into a new workbook
Replies
3
Views
130
rlv01
R
S
  • Question
Consolidate or Similar, Only with Formula Reference?
Replies
0
Views
59
Scotster
S
L
  • Question
Copy Subtotals from Source Workbook to Current Workbook
Replies
1
Views
293
DanteAmor
DanteAmor
L
  • Question
Button to import data from another workbook
Replies
5
Views
624
Lelewiwi
L
N
  • Question
How to get relative worksheet references when copying links in Excel?
Replies
1
Views
432
ndel40
N

Forum statistics

Threads
1,216,524
Messages
6,131,176
Members
449,629
Latest member
Mjereza

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top