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

[kishengiggs1]

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.

 

[John Davis]

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.

Maybe something like this?

Sub kishengiggs1()
Dim lr As Long
Dim lr2 As Long
Dim sht As Integer

For sht = 1 To 3

lr = Workbooks("workbook1.xls").Sheets("Sheet" & sht).Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Workbooks("workbook2.xls").Sheets("Sheet" & sht).Cells(Rows.Count, 1).End(xlUp).Row + 1

Workbooks("workbook1.xls").Sheets("Sheet" & sht).Range("A2:C" & lr).Copy Workbooks("workbook2.xls").Sheets("Sheet" & sht).Range("A" & lr2)

Next sht

End Sub

 

[kishengiggs1]

I am sorry if i did not explain this to you properly. I am kind of new to macros.

What i am trying to do is to create a master workbook.

I want to copy three columns from sheet 1, and 2 of workbook1 to another workbook which is workbook4.

Then, i want to copy three columns again from sheet 1 and 2 of workbook2 to workbook4

Then same process for 2 more sheets from same workbook. I want to make sure that for each column that is pasted the next pasting should start from where it ended.

thanks

 

[John Davis]

I am sorry if i did not explain this to you properly. I am kind of new to macros.

What i am trying to do is to create a master workbook.

I want to copy three columns from sheet 1, and 2 of workbook1 to another workbook which is workbook4.

Then, i want to copy three columns again from sheet 1 and 2 of workbook2 to workbook4

Then same process for 2 more sheets from same workbook. I want to make sure that for each column that is pasted the next pasting should start from where it ended.

thanks

OK. Sorry I misunderstood.

Which three columns from Sheets 1 and 2 will go too workbook4?

Where will they be placed in workbook4?

Which three columns from workbook2 Sheets 1 and 2 will go too workbook4 and where?

When you say "the same process for 2 more sheets from same workbook" - What are the other worksheets? And does "same workbook" mean workbook1 or workbook2?

Does everything get pasted into workbook4 in different sheets?

 

[kishengiggs1]

I am really sorry if i had confused you. I will make sure it will be clear this time.

So everything is going to be pasted in one worksheet in workbook4.By the way workbook4 has multiple sheets but we are only going to be pasting everything into one sheet.

First, from workbook1(sheet1)(columns D,E and AC) to be pasted in a sheet in workbook4.

Second, from workbook1(sheet2)(columns D,E and AC) to be pasted in the same sheet in workbook4 as above from where it ended last.

Third, from workbook2(sheet1)(columns D,E,C) to be pasted in the same worksheet in workbook4. I want to make sure its columns D,E and C in order NOT C,D,E.

Fourth, from workbook2(sheet2)(columns D,E,C) to be pasted in the same worksheet in workbook4. I want to make sure its columns D,E and C in order NOT C,D,E.

Fifth, from workbook3(sheet1)(columns D,E and C) to be pasted in same worksheet in workbook4. Order is important again.

Sixth, from workbook3(sheet2)(columns D, E and C) to be pasted in same worksheet in workbook4. Order is important.

Thats all. If possible i want all this 3 columns to be pasted in columns A,B,C in a sheet in workbook4. Everything should be in order. For ex: all the steps first to sixth should happen in order and it should paste from where it ended last.

thanks a alot!!

 

[John Davis]

I am really sorry if i had confused you. I will make sure it will be clear this time.

So everything is going to be pasted in one worksheet in workbook4.By the way workbook4 has multiple sheets but we are only going to be pasting everything into one sheet.

First, from workbook1(sheet1)(columns D,E and AC) to be pasted in a sheet in workbook4.

Second, from workbook1(sheet2)(columns D,E and AC) to be pasted in the same sheet in workbook4 as above from where it ended last.

Third, from workbook2(sheet1)(columns D,E,C) to be pasted in the same worksheet in workbook4. I want to make sure its columns D,E and C in order NOT C,D,E.

Fourth, from workbook2(sheet2)(columns D,E,C) to be pasted in the same worksheet in workbook4. I want to make sure its columns D,E and C in order NOT C,D,E.

Fifth, from workbook3(sheet1)(columns D,E and C) to be pasted in same worksheet in workbook4. Order is important again.

Sixth, from workbook3(sheet2)(columns D, E and C) to be pasted in same worksheet in workbook4. Order is important.

Thats all. If possible i want all this 3 columns to be pasted in columns A,B,C in a sheet in workbook4. Everything should be in order. For ex: all the steps first to sixth should happen in order and it should paste from where it ended last.

thanks a alot!!

Hope this works. Make sure you try it on a sample first. It assumes that Sheet1 in workbook4 is the destination sheet.

Sub kishengiggs1()
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("workbook1.xls").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr1 = Workbooks("workbook1.xls").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Workbooks("workbook2.xls").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr2a = Workbooks("workbook2.xls").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr3 = Workbooks("workbook2.xls").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr3a = Workbooks("workbook2.xls").Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
lr4 = Workbooks("workbook4.xls").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

Workbooks("workbook1.xls").Sheets("Sheet1").Range("D2:E" & lr).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("A" & lr4)
Workbooks("workbook1.xls").Sheets("Sheet1").Range("AC2:AC" & lr).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("C" & lr4)
Workbooks("workbook1.xls").Sheets("Sheet2").Range("D2:E" & lr1).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("A" & lr4)
Workbooks("workbook1.xls").Sheets("Sheet2").Range("AC2:AC" & lr1).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("C" & lr4)
Workbooks("workbook2.xls").Sheets("Sheet1").Range("D2:E" & lr2).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("A" & lr4)
Workbooks("workbook2.xls").Sheets("Sheet1").Range("C2:C" & lr2).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("C" & lr4)
Workbooks("workbook2.xls").Sheets("Sheet2").Range("D2:E" & lr2a).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("A" & lr4)
Workbooks("workbook2.xls").Sheets("Sheet2").Range("C2:C" & lr2a).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("C" & lr4)
Workbooks("workbook3.xls").Sheets("Sheet1").Range("D2:E" & lr3).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("A" & lr4)
Workbooks("workbook3.xls").Sheets("Sheet1").Range("C2:C" & lr3).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("C" & lr4)
Workbooks("workbook3.xls").Sheets("Sheet2").Range("D2:E" & lr3a).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("A" & lr4)
Workbooks("workbook3.xls").Sheets("Sheet2").Range("C2:C" & lr3a).Copy Workbooks("workbook4.xls").Sheets("Sheet1").Range("C" & lr4)



End Sub

 

[kishengiggs1]

I think it looks good but i am getting an error. On line:

lr = Workbooks("workbook1.xls").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

i am getting error saying subscript out of range.

 

[John Davis]

I think it looks good but i am getting an error. On line:

lr = Workbooks("workbook1.xls").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

i am getting error saying subscript out of range.

Do you have a workbook1 ? Does it have a Sheet1?

 

[kishengiggs1]

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

 

[kishengiggs1]

I am also getting object required error on this line:

lr = Workbooks(Book1.xlsx).Sheets(Sheet1).Cells(Rows.Count, 1).End(xlUp).Row

How do i solve this?