Copy Array in 1 Workbook Paste Array in Another with LastRow

[mrmmickle1]

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"

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!!

 

[L. Howard]

Did you forget to post the link to the second file?

Howard

 

[mrmmickle1]

Howard,

Here is the Link: https://www.dropbox.com/s/86itqyq5qiumju2/ArrayWBExample2.xlsx

 

[L. Howard]

I might need a little help from one of the pros to get this exactly correct...

Redim Preserve allows you to change the dimensions of an array while keeping the contents of the array.

I am shaky exactly about what the Redim actually does within the array, maybe it changes 0, 1, 2, 3, 4, 5, 6 to 1, 2, 3, 4, 5, 6, 7...?

For Each rngC In myRng
myArr(i) = rngC

Here i = the array element number and is increased by 1 using the i = i + 1 on each Next until there are no more elements.

So myArr(i) on the third Next would refer to K22 in the array.


This comes up from the bottom to find the last occupied cell and the (2) offsets to the next row which will be blank. If you used (1) then it would over write the last entry and if you used (3) it would provide a blank row between the last entry and the next new entry.

Range("B" & Cells(Rows.Count, "B").End(xlUp).Row)(2)

The resize is using the number of elements in the array to determine the resize. The ranges have to be the same size I do believe.

So it is like Range("A1").Resize(1, 7) which would be Range("A1:G7) 1 row and 7 columns.

.Resize(columnsize:=myRng.Cells.Count)


And in full disclosure, I keep examples of complex coding, knowing WHAT they do while not knowing HOW they do it. Once in awhile I am struck by lightning and it dawns on me as to HOW.

Also you can, as I often do, google something like this and there are many choices to read about REDim & Preserve, often with examples.
excel vba code what does ReDim Preserve myArr do

My standard preface is for a code search item is: excel vba code ... "subject matter"

Howard

 

[L. Howard]

Okay, just gave the Destination 2 sheet a look see.

This MAY not be practical using an array.

Seems doable enough using additional coding, just have to give each cell its own destination coding and we are off to the races.

I will give it a ponder and get back to you.

Howard

 

[mrmmickle1]

Howard,

I want to thank you for a very comprehensive explanation of the vba code that you provided. It really helps to put the smaller aspects of the code into perspective. In the end as you mention:

And in full disclosure, I keep examples of complex coding, knowing WHAT they do while not knowing HOW they do it. Once in awhile I am struck by lightning and it dawns on me as to HOW.

It is often easy to see what the end result of few lines of code are without fully grasping what the code is doing step by step. I am of course guilty of the same practice. I believe in this case you have done a splendid job of explaining. The code you provided for the first example will indeed help me in one portion of my current predicament. This is a world better then I was this morning

I hope to see you have success at orchestrating the second example I have provided. This would fully resolve my issues. The task of this macro is to greatly speed up data entry into the aforementioned logs. Before, this information was being entered in manually by typing or copying and pasting successive times. I really appreciate your assistance and look forward to hearing back from you in the coming days.

Thank you so much for your time and efforts!

 

[L. Howard]

Try these two downloads.

One is the ArrayWBExample2 workbook and the other is the ArrayDestination2 workbook

Once open you will need to save each workbook as a macro- enabled workbook and eliminate the "Drop Box" from the name.

See comment on ArrayWBExample2 workbook and the notes on the ArrayDestination2 workbook sheet Destination 1, yellow high-lighted.

In the code in the ArrayWBExample2 module will need to change where it opens the ArrayDestination2 workbook. See the commented line just above the code that needs the file path changed to your file path.

Only after these changes are made can you test to see if it does what you want.

On the Source sheet there are two buttons, one to copy to Destination 1 sheet and one for Destination 2 sheet.

When clicking the buttons, if ArrayDestination2 workbook is NOT open, there is code to open it, the copy will take place, and you will be left on one of the ArrayDestination2 sheets.

Where you want the focus to be after a copy is completed is something you will need to decide, and then we can add code to make that happen.

Each copy also saves the ArrayDestination2 workbook. It can also close it if you want. Then reopen with the next copy, save and close each time. Up to you.


https://www.dropbox.com/s/avjm8qgyzuomm4a/ArrayDestination2 Drop Box.xlsm

https://www.dropbox.com/s/f1jbb52eycwky0b/ArrayWBExample2 Drop Box.xlsm

Give a try and see what changes to make or if it is really doing the work you want..

I will download both workbooks after posting this to be sure proper copies got to you.

Howard

 

[mrmmickle1]

Howard,

Oh Man oh Man!!! Talk about incredible. This was well done sir! Well done! The explanation of what I needed to do to change the code was very well explained and wow it worked on the first go....and the second and the third ahahahah! Thank you so much Now I just need to re-write the code in my project to reflect these examples! I really appreciate the help. I am very grateful. You have solved a huge problem I was encountering. Thank you for sticking with me and helping me solve this issue. It means a great deal to me.

 

[L. Howard]

And you are quite welcome. Fun when it works out as well as it has.

You might run into something you want changed as you use the books, but seems you might be able to make those changes yourself. If not post back. May stump both of us.

Where 'bouts are you in Tar Heel country.

I used to fight forest fires in the Washington, Plymouth New Bern area years ago. Good little Mexican food eatery just off campus of ECU in Greenville.

Regards,
Howard

 

[mrmmickle1]

Howard,

Thanks again. I will be re-writing the code over the next few days. If I run into any issues or obstacles I will keep you posted. I doubt I could stump you. You seem pretty on top of your game!

As far as Tar Heel Country goes... I grew up in Greensboro, NC. Then, I went to school at UNC-CH for a few years and ended up finishing at Guilford College. I'm somewhat familiar with the Greenville area. I have been down there for several ECU football games. Currently, I am living in the Queen City. Small world huh

You'll have to let me in on that little Mexican Place. I love trying new restaurants!

Hope all is well. Thanks again for the help. I'll let you know how my current project ends up.

 

[L. Howard]

Near the bottom of both codes for dest 1 and dest 2 note the line here.

Application.ScreenUpdating = False

Should be set to True

Doesn't seem to bother the code much, if any, but none the less should be True.

Howard