• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying data from differenct Workbooks, and using Loops

Shasta Webb

New Member
I am very new to VBA and really having a hard time with this. I have finally gotten code written to seemingly do what I need but alas, it doesnt seem to work. I would appreciate any help that can be had, because I am SO Lost!

What I am trying to do is copy data from select cells on each row of my original workbook (ALPHA) and paste that data into the correct cells of an established workbook (REPORT).

The ALPHA file name will be different with each report, so Ive tried to use a MsgBox to input the file name that I am copying from.

Also, I will need to repeat copying and pasting the data for only 36 rows at a time, as that is what the REPORT workbook is set up for. Once a sheet is full, I will have to add a new sheet to the REPORT workbook and continue the loop process, until it has reached the last row with data in the original workbook. The number of rows will always be different based on whatever ALPHA workbook I am working with at the time.

I am uploading the sample files that I am using for clarification. The particular cells I am needing to copy from and paste to should be clear in the last part of my code.

Other details:
While the ALPHA workbook data is separated every other row, the REPORT workbook will need to be listed row by row with no spaces, with the pasted data beginning at E15.

Thank you so much for any advice!! I wouldn't be surprised if my code is SO wrong, but Ive tried to work with it the best I could understand!!

My Code is as Follows:

Code:
Sub LoopPaste()

' Louisiana Report Macro
' Keyboard Shortcut: Ctrl+l

Dim wkSourceName As String
Dim wkDestination As String
Dim i As Long
Dim ii As Long

Application.ScreenUpdating = False

'Enter the name of the workbook that you want to copy data from including the .XLS
wkSourceName = InputBox("Enter the name of the workbook that you want to copy data from including the .XLS")
wkDestination = ActiveWorkbook.Name


'Find last Row in Column A with Data
LastRow = wkSourceName.Range("A:A").Find("*", searchdirection:=xlPrevious).Row
ii = 15


'Begin Loop
For i = 8 To LastRow
wkDestination.Range("E" + i) = wkSourceName.Range("A" + ii).Value
wkDestination.Range("N" + i) = wkSourceName.Range("C" + ii).Value
wkDestination.Range("Q" + i) = wkSourceName.Range("D" + ii).Value
wkDestination.Range("J" + i) = wkSourceName.Range("H" + ii).Value
wkDestination.Range("T" + i) = wkSourceName.Range("I" + ii).Value
wkDestination.Range("O" + i) = wkSourceName.Range("K" + ii).Value
wkDestination.Range("P" + i) = wkSourceName.Range("L" + ii).Value
wkDestination.Range("L" + i) = wkSourceName.Range("N" + ii).Value
wkDestination.Range("M" + i) = wkSourceName.Range("P" + ii).Value

ii = ii + 1

Next i
   
  Application.ScreenUpdating = True
End Sub
 
Back
Top