View Full Version : Copying from 1 Workbook and multisheets to Another Workbook and multisheets
kewiopex
10-31-2016, 10:10 AM
I am very new in the VBA coding area and I would like to learn some more. I am trying to perfect the coding that will allow me to copy data ranges from 2 sheets ( Unit 1, Unit 2) into another workbook with the same sheet names but same data ranges and with a paste special of value only. When I run the code it gives me a runtime error where object does not support this operation or method. Any help would be greatly appreciated.
Sub TestWB()
Dim sourceWB As Workbook
Dim i As Long
Set sourceWB = Workbooks("TestBP.xls")
For i = 1 To Worksheets.Count
sourceWB.Worksheets("Unit" & i).Range("B2").Copy ThisWorkbook.Worksheets("Unit" & i).Range("B2").PasteSpecialxlPasteValues
sourceWB.Worksheets("Unit" & i).Range("B7").Copy ThisWorkbook.Worksheets("Unit" & i).Range("B7").PasteSpecialxlPasteValues
Next i
End Sub
Bob Phillips
11-01-2016, 02:58 AM
Sub TestWB()
Dim sourceWB As Workbook
Dim i As Long
Set sourceWB = Workbooks("TestBP.xls")
With sourceWB
For i = 1 To Worksheets.Count
.Worksheets("Unit" & i).Range("B2").Copy
ThisWorkbook.Worksheets("Unit" & i).Range("B2").PasteSpecial Paste:=xlPasteValues
.Worksheets("Unit" & i).Range("B7").Copy
ThisWorkbook.Worksheets("Unit" & i).Range("B7").PasteSpecial Paste:=xlPasteValues
Next i
End With
End Sub
kewiopex
11-01-2016, 10:06 AM
You are the best. Worked like a charm and I thank you for all your time and details. You will save me a ton of tie.
Sub xld()
'xld's sub modified by SamT
'Added Multiple Range capability with only one CopyPaste line inside a loop.
Dim RngAddies As Variant
Dim i As Long
Dim r As Long
RngAddies = Array("B2", "B7") 'Separate each additional address by a comma.
With Workbooks("TestBP.xls")
For i = 1 To Worksheets.Count
For r = LBound(RngAddies) to UBound(RngAddies)
.Worksheets("Unit" & i).Range(RngAddies(r)).Copy
ThisWorkbook.Worksheets("Unit" & i).Range(RngAddies(r)).PasteSpecial Paste:=xlPasteValues
Next r
Next i
End With
End Sub
One line alternative to two line CopyPaste above.
ThisWorkbook.Worksheets("Unit" & i).Range(RngAddies(r)) = .Worksheets("Unit" & i).Range(RngAddies(r))
kewiopex
11-01-2016, 02:19 PM
Sub xld()
'xld's sub modified by SamT
'Added Multiple Range capability with only one CopyPaste line inside a loop.
Dim RngAddies As Variant
Dim i As Long
Dim r As Long
RngAddies = Array("B2", "B7") 'Separate each additional address by a comma.
With Workbooks("TestBP.xls")
For i = 1 To Worksheets.Count
For r = LBound(RngAddies) to UBound(RngAddies)
.Worksheets("Unit" & i).Range(RngAddies(r)).Copy
ThisWorkbook.Worksheets("Unit" & i).Range(RngAddies(r)).PasteSpecial Paste:=xlPasteValues
Next r
Next i
End With
End Sub
One line alternative to two line CopyPaste above.
ThisWorkbook.Worksheets("Unit" & i).Range(RngAddies(r)) = .Worksheets("Unit" & i).Range(RngAddies(r))
kewiopex
11-01-2016, 02:26 PM
SamT, this is so elegant! I am learning a lot from these superb responses. I have another issue that I think may reside in another question. I have come across another situation today whereby the worksheet names are different in each workbook but I wish to loop through the worksheets in one workbook and copy ranges and pasting in different worksheet names in another workbook. I noticed that coding names for worksheets use the Sheet1..notation but, in my example the worksheets in 1 workbook are not the same in the other workbook.
in my example the worksheets in 1 workbook are not the same in the other workbook.
How are they co-related?
kewiopex
11-01-2016, 04:38 PM
Worksheet names are slightly different in each workbook and not in the same order...eg Unit 1 , Unit 2 versus B-1 B-2. The data from source does not directly map over as stated in the original issue. Does this adequately describe the nuance?
Thank you once again for your tremendous effort.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.