gvreddyhr
03-04-2013, 07:11 AM
Hi,
I have the excel file which consolidates the data from different workbooks and puts in desired workbook, now I need small customization to that where I need to incorporate sheet name for copying the data because I have multiple sheets available on the workbooks.
As I said, I need to specify the sheet from which the specified ranges will be copied. I tweaked the code accordingly and added the columns in List worksheet which is not working, running the macro then produces the error message. My code is as follows. My offsets are different because I have a total of 9 columns (I specified a specific range into which the data should be pasted. Headings are as follows:
Item No
File Name
Full Path
Data Range Start Cell
Data Range End Cell
Copy to Sheet
Copy To Location(Start Cell Only)
Copy To Location(End Cell Only)
Which Sheet Copy
Sub GetData()
Dim strWhereToCopy As String, strStartCellRange As String
Dim strListSheet As String, strWhichSheetCopy As String
strListSheet = “List”
On Error GoTo ErrH
Sheets(strListSheet).Select
Range(“B2″).Select
‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> “”
strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strStartCellRange = ActiveCell.Offset(0, 5) & “:” & ActiveCell.Offset(0, 6)
strWhichSheetCopy = ActiveCell.Offset(0, 7).Value
Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
Sheets(strWhichSheetCopy).Range(strCopyRange).Select
Selection.Copy
currentWB.Activate
Sheets(strWhereToCopy).Select
Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub
ErrH:
MsgBox “It seems one or more files were missing. The data copy operation is not complete.”
Exit Sub
End Sub
Thanks in advance
-GVR
I have the excel file which consolidates the data from different workbooks and puts in desired workbook, now I need small customization to that where I need to incorporate sheet name for copying the data because I have multiple sheets available on the workbooks.
As I said, I need to specify the sheet from which the specified ranges will be copied. I tweaked the code accordingly and added the columns in List worksheet which is not working, running the macro then produces the error message. My code is as follows. My offsets are different because I have a total of 9 columns (I specified a specific range into which the data should be pasted. Headings are as follows:
Item No
File Name
Full Path
Data Range Start Cell
Data Range End Cell
Copy to Sheet
Copy To Location(Start Cell Only)
Copy To Location(End Cell Only)
Which Sheet Copy
Sub GetData()
Dim strWhereToCopy As String, strStartCellRange As String
Dim strListSheet As String, strWhichSheetCopy As String
strListSheet = “List”
On Error GoTo ErrH
Sheets(strListSheet).Select
Range(“B2″).Select
‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> “”
strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strStartCellRange = ActiveCell.Offset(0, 5) & “:” & ActiveCell.Offset(0, 6)
strWhichSheetCopy = ActiveCell.Offset(0, 7).Value
Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
Sheets(strWhichSheetCopy).Range(strCopyRange).Select
Selection.Copy
currentWB.Activate
Sheets(strWhereToCopy).Select
Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub
ErrH:
MsgBox “It seems one or more files were missing. The data copy operation is not complete.”
Exit Sub
End Sub
Thanks in advance
-GVR