Copy Array in 1 Workbook Paste Array in Another with LastRow

mrmmickle1

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
Not sure how to go about this process. I was hoping that someone could point me in the right direction. I need to copy and array of cells in one workbook and paste that array into another array of cells in the second workbook.

Copy WorkBook is "Example"
Paste Workbook is "Work"

Code: 
Sub CopyPasteBetweenWB()
Dim Finalrow As Long
Dim i           As Integer

finalRow = ActiveSheet.UsedRange.Rows.count + 1
                                                       
CopyArray = Array( "I4", "C28", "K22", "M23", "F27", "M22", "K23")
PasteArray = Array(2,3,4,5,6,7,8)  [COLOR=#008000]'Final Row of Activesheet in "Work" Workbook in Respective Columns B, C, D, E, F ,G, H[/COLOR]
[COLOR=#008000]
[/COLOR] For i = LBound(CopyArray) To UBound(CopyArray)
[COLOR=#008000]
[/COLOR]Windows(Example).Activate[COLOR=#008000]
[/COLOR]CopyArray(i).Copy

Windows(Work).Activate
.Range(finalRow & PasteArray(i) ).PasteSpecial xlPasteValues

Next i
End Sub

Not sure I am even on the right track. Any guidance with this issue would be great!!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
This may help to clarify what I am doing now and the result I am looking to get. I nbeleive it would be better to use arrays if possible. Otherwise I believe it is unreadable/unmanageable as it begins to extend too long to easily navigate. I am having doing this with 30 to 40 different cells throughout the code:
Code: 
                    Windows(Example).Activate
                    Range("I4").Copy
                    Windows(Work).Activate
                    Cells(finalRow, 2).Select
                    ActiveCell.PasteSpecial xlPasteValues
                 
                    Windows(Example).Activate
                    Range("C28").Copy
                    Windows(Work).Activate
                    Cells(finalRow, 3).Select
                    ActiveCell.PasteSpecial xlPasteValues
               
                    Windows(Example).Activate
                    Range("K22").Copy
                    Windows(Work).Activate
                    Cells(finalRow, 4).Select
                    ActiveCell.PasteSpecial xlPasteValues
              
                    Windows(Example).Activate
                    Range("M23").Copy
                    Windows(Work).Activate
                    Cells(finalRow, 5).Select
                    ActiveCell.PasteSpecial xlPasteValues
                
                    Windows(Example).Activate
                    Range("F27").Copy
                    Windows(Work).Activate
                    Cells(finalRow, 6).Select
                    ActiveCell.PasteSpecial xlPasteValues
                
                    Windows(Example).Activate
                    Range("M22").Copy
                    Windows(Work).Activate
                    Cells(finalRow, 7).Select
                    ActiveCell.PasteSpecial xlPasteValues
                 
                    Windows(Example).Activate
                    Range("K23").Copy
                    Windows(Work).Activate
                    Cells(finalRow, 8).Select
 
Upvote 0
bump.

This is the direction I have headed. Not sure if it will work or not. Any suggestions on fixing my issue are welcome!

Code: 
[FONT=arial]Sub CopyPasteBetweenWB()[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]Dim Finalrow      As Long[/FONT]
[FONT=arial]Dim i                 As Integer[/FONT]
[FONT=arial]Dim g                As Integer[/FONT]
[FONT=arial]Dim Work          As String[/FONT]
[FONT=arial]Dim Example     As String[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial][COLOR=#008000]'Can't get the code right.....[/COLOR][/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]Example = "Example"  [/FONT]
[FONT=arial]Work = "Work"[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial][COLOR=#008000]'Copy the CopyArray in "Example" workbook to Finalrow & PasteArray in "Work" workbook[/COLOR][/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]Finalrow = ActiveSheet.UsedRange.Rows.<wbr>Count + 1[/FONT]
[FONT=arial]  [/FONT]
[FONT=arial]CopyArray = Array("I4", "C28", "K22", "M23", "F27", "M22", "K23")[/FONT]
[FONT=arial]PasteArray = Array("2", "3", "4", "5", "6", "7", "8")[COLOR=#008000] ' Will not always be in order, could be random, 
[/COLOR][/FONT]
[FONT=arial][COLOR=#008000]                                                                         ' These represent the columns B, C, D, E, F, G, H[/COLOR][/FONT]
[FONT=arial]   For i = LBound(CopyArray) To UBound(CopyArray)[/FONT]
[FONT=arial]   For g = LBound(PasteArray) To UBound(PasteArray)[/FONT]
[FONT=arial] [/FONT]
[FONT=arial]Windows(Example).Activate [COLOR=#008000]'The sheet for the workbook is "Sheet1"[/COLOR][/FONT]
[FONT=arial]Range(i).Copy [COLOR=#008000]'Code gets stuck on this line.  I don't think I am passing the array correctly...[/COLOR][/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]Windows(Work).Activate [COLOR=#008000]'The sheet for the workbook is "Sheet1"[/COLOR][/FONT]
[FONT=arial]Range(Finalrow, g).PasteSpecial xlPasteValues[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]Next i[/FONT]
[FONT=arial]Next g[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]End Sub[/FONT]
 
Last edited:
Upvote 0
Code: 
CopyArray = Array("I4", "C28", "K22", "M23", "F27", "M22", "K23")
PasteArray = Array("2", "3", "4", "5", "6", "7", "8") ' Will not always be in order, could be random,

So is the PasteArray random in the sense that each copy the pastarray could change or is it always the same but in a jumbled order something like:

PasteArray = Array("8", "6", "4", "5", "3", "7", "2") and always goes to those columns?

And do you want to overwrite previous copies or .End(xlUp).Offset(0 ,1), each copy below the last?

Regards,
Howard
 
Upvote 0
L. Howard,

Thank you for the response. The PasteArray could possibly contain different numbers (Specifying different Columns). I am trying to copy the above cells into the last row of Data Log of sorts. So the information would always be input on the LastRow + 1. In order to place the data onto an empty row. The information is entered into one data log in the order above:
Code: 
PasteArray = Array("2", "3", "4", "5", "6", "7", "8")

But for example in another data log the same information will be entered into different columns so it would be something like this:
Code: 
PasteArray = Array("4", "6", "9", "11", "15", "16", "18")

The freshly pasted values in workbook "Work" would look like this:

Excel 2012
ABCDEFGH
1HeaderHeaderHeaderHeaderHeaderHeaderHeaderHeader
27/2/2014Example!I4.ValueExample!C28.ValueExample!K22.ValueExample!M23.ValueExample!F27.ValueExample!M22. ValueExample!K23.Value
WORK




I hope this makes sense. Please let me know if you need further clarification. I would be glad to explain further.
 
Upvote 0
Sorry,
I just realized I did not depict the above picture example accurately The Example! should be [Example] as it is a workbook not a worksheet. My apologies. If it is of any consequence it would be [Example]Sheet1!I4.Value ......etc.
 
Upvote 0
Can you post a link to an example workbook, and let sheet 1 represent the "source workbook" and sheet 2 represent the "destination workbook"?

Then on both sheets hand enter a small sample of data for the FROM sheet and the TO sheet of how it would look if successful copies are made.

Even thought its the same workbook, that would give an enlightened view of the scheme.

Howard
 
Upvote 0
Here's my first shot at it.

Copy to the two codes to a standard module. Using the example workbook you posted the link for.

The first code does not carry the date to Destination, and there was no cell in the array to represent the date cell.
This code copies to column B, Resized, and then next row for next copies.

The other code includes the date and copies to column A, Resized, and on down.

I UNMERGED the I4/J4 cells and used Center Across Selection, I4 is the cell in use. (Merged cells can be a nuisance)

Is this a step in the right direction?

This from your post #5 still puzzles me.
Code: 
But for example in another data log the same information will be entered into different columns so it would be something like this:

PasteArray = Array("4", "6", "9", "11", "15", "16", "18")

Howard

Code: 
Option Explicit

Sub MyRevampEXP()
Dim myRng As Range
Dim rngC As Range
Dim i As Long
Dim myArr() As Variant

Set myRng = Range("I4, C28, K22, M23, F27, M22, K23")

For Each rngC In myRng
    ReDim Preserve myArr(myRng.Cells.Count - 1)
    myArr(i) = rngC
    i = i + 1
Next

Sheets("Destination").Range("B" & Cells(Rows.Count, "B").End(xlUp).Row) _
                     .End(xlUp)(2).Resize(columnsize:=myRng.Cells.Count) = myArr ' to a row

End Sub


'////////********************************///////////


Sub MyRevampEXP_Date()
Dim myRng As Range
Dim rngC As Range
Dim i As Long
Dim myArr() As Variant

Set myRng = Range("E4,I4, C28, K22, M23, F27, M22, K23")

For Each rngC In myRng
    ReDim Preserve myArr(myRng.Cells.Count - 1)
    myArr(i) = rngC
    i = i + 1
Next

Sheets("Destination").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row) _
                     .End(xlUp)(2).Resize(columnsize:=myRng.Cells.Count) = myArr ' to a row

End Sub
 
Upvote 0
L.Howard,

Wow both of these examples work great. I understand why you completed the second MyRevampEXP_Date() example as well. The Date in the Destination is just Today(). It is not taken from E4. Thank you for the extra example though! It will prove useful. I have included another example file to explain the second PasteArray. I believe I could have been clearer. Maybe this file can help to explain the confusion. 2nd Example . THis example just involves different cell destinations.


I am a little unsure of how the code works though. Can you clear up a few points please so I can better understand how the code works?.


Code: 
Option Explicit
Sub MyRevampEXP()
Dim myRng As Range
Dim rngC As Range
Dim i As Long
Dim myArr() As Variant

Set myRng = Range("I4, C28, K22, M23, F27, M22, K23")   [COLOR=#008000]'Sets Array

[/COLOR]For Each rngC In myRng [COLOR=#008000]' For Each Cell in the myRng Array do....[/COLOR]    
ReDim Preserve myArr(myRng.Cells.Count - 1)   [COLOR=#008000]'What does Redim Preseve do?  myArr(myrng.cells.count-1)????  Not sure I get this....    

[/COLOR]myArr(i) = rngC    

i = i + 1
Next

Sheets("Destination").Range("B" & Cells(Rows.Count, "B").End(xlUp).Row) _          [COLOR=#008000]  'Counts rows in B then what does (2) do?  Resize? Pastes to new sheet????[/COLOR]                     .End(xlUp)(2).Resize(columnsize:=myRng.Cells.Count) = myArr ' to a row

End Sub

Any further explanation would be great. I would love to use this knowledge in the future. Just a little new to this. I really appreciate the help. YOu are a lifesaver. The code workks great. I would just like to learn the logic behind it and figure out how I can use it in more instances.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
VBA to Copy Multiple sheets to new workbook & save as .xlsb or .xlsx
2
Replies
16
Views
671
rahildhody
R
M
  • Question
Error 1004 - Can't Copy as Picture, but only on certain computer
2
Replies
10
Views
314
rlv01
R
B
  • Question
Copy Paste from one Workbook to another
Replies
1
Views
216
rollis13
rollis13
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
217
Insurance Dave
I
H
  • Solved
How to copy data from one file and paste it into a dynamically named workbook
Replies
3
Views
175
dermie_72
D

Forum statistics

Threads
1,214,827
Messages
6,121,823
Members
449,049
Latest member
cybersurfer5000

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