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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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

L
  • Question Question
Moving and renaming multiple workbooks from a master workbook.
Replies
1
Views
279
Luke1690
L
C
  • Question Question
Updating both sheets in two workbooks from either sheet source
Replies
6
Views
248
chazrab
C
A
  • Question Question
Need advise on code
2
Replies
11
Views
206
AlexanderBB
A
L
  • Solved
Macro to open multiple workbooks and copy a range of data based on todays date. Then paste the data in master workbook in one sheet under each other.
Replies
8
Views
662
Luke1690
L
A
  • Solved
VBA to declare column headers and use in further code
Replies
6
Views
472
AggieJess27
A

Forum statistics

Threads
1,225,149
Messages
6,183,179
Members
453,151
Latest member
Lizamaison

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