Nicolaf
08-20-2013, 05:09 AM
Hi,
I have done a macro to look for a cell in a worksheet and copy paste it into another worksheet of a different workbook.
Code below:
Private Sub CommandButton1_Click()
IName = ThisWorkbook.Sheets("List").Range("B7").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A7").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Calculate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Fixings.xls").Activate
Sheets("List").Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
ActiveWindow.Close
IName = ThisWorkbook.Sheets("List").Range("B8").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A8").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Calculate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Fixings.xls").Activate
Sheets("List").Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
ActiveWindow.Close
End Sub
So in file Fixings.xls in worksheet List we have the workbook to open (file name is in cell B7).
The worksheet to open is found in worksheet List in cell A7.
Once cell M6 has been copied then we move to next workbook found in cell B8 and next worksheet found in cell A8.
What I would like to do is a loop so that instead of repeating code for cell B7/A7, B8/A8 etc. it simply does this automatically for all data found in columns A and B of Fixings.xls until no more data is found and macro stops.
How can I do this?
Let me know if something not clear.
Thanks,
Nix
:dunno
I have done a macro to look for a cell in a worksheet and copy paste it into another worksheet of a different workbook.
Code below:
Private Sub CommandButton1_Click()
IName = ThisWorkbook.Sheets("List").Range("B7").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A7").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Calculate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Fixings.xls").Activate
Sheets("List").Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
ActiveWindow.Close
IName = ThisWorkbook.Sheets("List").Range("B8").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A8").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Calculate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Fixings.xls").Activate
Sheets("List").Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
ActiveWindow.Close
End Sub
So in file Fixings.xls in worksheet List we have the workbook to open (file name is in cell B7).
The worksheet to open is found in worksheet List in cell A7.
Once cell M6 has been copied then we move to next workbook found in cell B8 and next worksheet found in cell A8.
What I would like to do is a loop so that instead of repeating code for cell B7/A7, B8/A8 etc. it simply does this automatically for all data found in columns A and B of Fixings.xls until no more data is found and macro stops.
How can I do this?
Let me know if something not clear.
Thanks,
Nix
:dunno