Commander Vimes
New Member
- Joined
- Aug 8, 2016
- Messages
- 11
Hello,
I need help with some VBA coding, my requirement is: I need to copy and paste ranges from 21 excel docs in a folder to a Master workbook withinrelevant named sheets. I have some code, but want to loop it so it runs through each of the docs and copys+pastes the relevant parts into the relevant sectionsof the MasterWorkbook<o
></o
>
<o
> </o
>
Example:<o
></o
>
Master Workbook has the below worksheets:<o
></o
>
>>INPUT N1<o
></o
>
>>INPUT N2<o
></o
>
>>INPUT D1<o
></o
>
>>INPUT C1<o
></o
>
<o
></o
>
Document 1 of 21 (as do the other 20) has the below sheets:<o
></o
>
N1<o
></o
>
N2<o
></o
>
D1<o
></o
>
C1<o
></o
>
<o
></o
>
I have code (see below) to copy range in N1 to referencecell in >>INPUT N1 (Application.Goto Reference:="R601C2") and so on for the other 3. Now I want to loopthe code so it repeats the process for the remaining docs and changes the references so that they are correct.<o
></o
>
<o
></o
>
Each doc 1-21 should paste the info into a different section eg <o
></o
>
Doc 1 Application.Goto Reference:="R1C2"<o
></o
>
Doc 2 Application.Goto Reference:="R101C2"<o
></o
>
Doc 3 Application.Goto Reference:="R201C2"<o
></o
>
Etc <o
></o
>
<o
></o
>
So how do I get the Code to loop and change the document itopens and the reference it pastes to in the Master Workbook?<o
></o
>
<o
></o
>
So I want the code to run from the Master Workbook and:
></o
>
<o
></o
>
My current CODE:<o
></o
>
<o
></o
>
<o
></o
>
I need help with some VBA coding, my requirement is: I need to copy and paste ranges from 21 excel docs in a folder to a Master workbook withinrelevant named sheets. I have some code, but want to loop it so it runs through each of the docs and copys+pastes the relevant parts into the relevant sectionsof the MasterWorkbook<o


<o


Example:<o


Master Workbook has the below worksheets:<o


>>INPUT N1<o


>>INPUT N2<o


>>INPUT D1<o


>>INPUT C1<o


<o


Document 1 of 21 (as do the other 20) has the below sheets:<o


N1<o


N2<o


D1<o


C1<o


<o


I have code (see below) to copy range in N1 to referencecell in >>INPUT N1 (Application.Goto Reference:="R601C2") and so on for the other 3. Now I want to loopthe code so it repeats the process for the remaining docs and changes the references so that they are correct.<o


<o


Each doc 1-21 should paste the info into a different section eg <o


Doc 1 Application.Goto Reference:="R1C2"<o


Doc 2 Application.Goto Reference:="R101C2"<o


Doc 3 Application.Goto Reference:="R201C2"<o


Etc <o


<o


So how do I get the Code to loop and change the document itopens and the reference it pastes to in the Master Workbook?<o


<o


So I want the code to run from the Master Workbook and:
- Open doc 1<o
></o
>
- Copy N1 range<o
></o
>
- Paste into Master Workbook >>INPUT N1 specified range<o
></o
>
- Do this for N2, D1,C1 aswell
- Close Doc 1<o
></o
>
- Repeat process for Doc 2, 3, 4 etc<o
></o
>


<o


My current CODE:<o


<o


Rich (BB code):
SubCopyInputSheetData()<o:p></o:p>
'<o:p></o:p>
' CopyInputSheetData Macro<o:p></o:p>
'<o:p></o:p>
<o:p> </o:p>
'<o:p></o:p>
<o:p> </o:p>
' Preventsscreen refreshing.<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p> </o:p>
'Timer Start<o:p></o:p>
Dim i As Integer<o:p></o:p>
<o:p> </o:p>
Dim StartTime As Double<o:p></o:p>
Dim SecondsElapsed As Double<o:p></o:p>
<o:p> </o:p>
StartTime = Timer<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
'*************************************************************************************************<o:p></o:p>
<o:p> </o:p>
'Need to change File location to the correct one filesare stored in<o:p></o:p>
Workbooks.OpenFilename:= _<o:p></o:p>
"filelocation for Document 1.xlsm"<o:p></o:p>
<o:p> </o:p>
'N1<o:p></o:p>
'This is the copy section to copy N1 from Sheet openedabove<o:p></o:p>
Sheets("N1").Select<o:p></o:p>
Range("A5").Select<o:p></o:p>
Selection.CurrentRegion.Select<o:p></o:p>
Selection.Copy<o:p></o:p>
<o:p></o:p>
Windows("MasterWorkbook.xlsm")._<o:p></o:p>
Activate<o:p></o:p>
Sheets(">>INPUT N1").Select<o:p></o:p>
Application.Goto Reference:="R601C2"<o:p></o:p>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _<o:p></o:p>
:=False,Transpose:=False<o:p></o:p>
<o:p> </o:p>
'N2<o:p></o:p>
Windows("Document 1.xlsm"). _<o:p></o:p>
Activate<o:p></o:p>
Sheets("N2").Select<o:p></o:p>
Range("A5").Select<o:p></o:p>
Selection.CurrentRegion.Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Windows("MasterWorkbook.xlsm")._<o:p></o:p>
Activate<o:p></o:p>
Sheets(">>INPUT N2").Select<o:p></o:p>
Application.Goto Reference:="R601C2"<o:p></o:p>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _<o:p></o:p>
:=False,Transpose:=False<o:p></o:p>
<o:p> </o:p>
'D1<o:p></o:p>
Windows("Document 1.xlsm"). _<o:p></o:p>
Activate<o:p></o:p>
Sheets("D1").Select<o:p></o:p>
Range("A5").Select<o:p></o:p>
Selection.CurrentRegion.Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Windows("MasterWorkbook.xlsm")._<o:p></o:p>
Activate<o:p></o:p>
Sheets(">>INPUT D1").Select<o:p></o:p>
Application.Goto Reference:="R601C2"<o:p></o:p>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _<o:p></o:p>
:=False,Transpose:=False<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
'C1<o:p></o:p>
Windows("Document 1.xlsm"). _<o:p></o:p>
Activate<o:p></o:p>
Sheets("C1").Select<o:p></o:p>
Range("A5").Select<o:p></o:p>
Selection.CurrentRegion.Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Windows("MasterWorkbook.xlsm")._<o:p></o:p>
Activate<o:p></o:p>
Sheets(">>INPUT C1").Select<o:p></o:p>
Application.Goto Reference:="R601C2"<o:p></o:p>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _<o:p></o:p>
:=False,Transpose:=False<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Workbooks("Document 1.xlsm").Close<o:p></o:p>
<o:p> </o:p>
'*************************************************************************************************<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
' Enablesscreen refreshing.<o:p></o:p>
Application.ScreenUpdating = True<o:p></o:p>
<o:p> </o:p>
SecondsElapsed =Round(Timer - StartTime, 2)<o:p></o:p>
MsgBox "Input Sheet Data has now been imported, in " & SecondsElapsed &" seconds", vbInformation<o:p></o:p>
End Sub

