I am pretty green in VBA, have 1 master spreadsheet namely CEO(i.e. provide an insight on every monday for last week performance , i want to verify if certain data is correct from 10 different spreadsheet (i.e. last weekend data, each date with 2 spreadsheet)
The spreadsheet path is saved in one of the spreadsheet namely link , CEO's link is in B3 while the weekly data is from B18 to B27
I dont know how to put those code into function so that I can call the function 10 times ? would any master please help me ? Thank you so much!
Sub handlesheet()
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim Sht As Worksheet
Dim i As Long
Dim lrow As Long
Dim b As Long
'initalize spreadsheet
Sheets("CEO").Cells.Clear
Sheets("A1").Cells.Clear
Sheets("A2").Cells.Clear
Sheets("B1").Cells.Clear
Sheets("B2").Cells.Clear
Sheets("C1").Cells.Clear
Sheets("C2").Cells.Clear
Sheets("D1").Cells.Clear
Sheets("D2").Cells.Clear
Sheets("E1").Cells.Clear
Sheets("E2").Cells.Clear
Set wbTarget = ThisWorkbook
wbPath = ThisWorkbook.Sheets("Link").Range("B3").Value
'Copy CEO data to the CEO tab
Set wbSource = Workbooks.Open(wbPath)
With wbSource.Sheets("Sheet1").UsedRange
'Now, paste to y worksheet:
wbTarget.Sheets("CEO").Range("A1").Resize( _
.Rows.Count, .Columns.Count) = .Value
End With
wbSource.Close
' how to rewrite with a sub function so that I can call 10 times for A1, A2,B1,B2,C1,C2,D1,D2,E1,E2
' copy the spreadsheet from source to A1 tab, After that , if any cell in column A (ie. from A6) is a number greater than 0 ,sum up the related cell in column D and 'put the total in another spreadsheet on the same workbook fro statistic use
'A1
wbPath = ThisWorkbook.Sheets("Link").Range("B18").Value
Filename = Dir(wbPath, vbNormal)
If Filename <> "" Then
Set wbSource = Workbooks.Open(wbPath)
With wbSource.Sheets("Sheet1").UsedRange
'Now, paste to y worksheet:
wbTarget.Sheets("A1").Range("A1").Resize( _
.Rows.Count, .Columns.Count) = .Value
End With
wbSource.Close
lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 6 To lrow
If IsNumeric(Cells(i, 1)) And Cells(i, 1) > 0 Then
Cells(i, 10).Value = Cells(i, 4).Value
End If
Next i
End If
The spreadsheet path is saved in one of the spreadsheet namely link , CEO's link is in B3 while the weekly data is from B18 to B27
I dont know how to put those code into function so that I can call the function 10 times ? would any master please help me ? Thank you so much!
Sub handlesheet()
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim Sht As Worksheet
Dim i As Long
Dim lrow As Long
Dim b As Long
'initalize spreadsheet
Sheets("CEO").Cells.Clear
Sheets("A1").Cells.Clear
Sheets("A2").Cells.Clear
Sheets("B1").Cells.Clear
Sheets("B2").Cells.Clear
Sheets("C1").Cells.Clear
Sheets("C2").Cells.Clear
Sheets("D1").Cells.Clear
Sheets("D2").Cells.Clear
Sheets("E1").Cells.Clear
Sheets("E2").Cells.Clear
Set wbTarget = ThisWorkbook
wbPath = ThisWorkbook.Sheets("Link").Range("B3").Value
'Copy CEO data to the CEO tab
Set wbSource = Workbooks.Open(wbPath)
With wbSource.Sheets("Sheet1").UsedRange
'Now, paste to y worksheet:
wbTarget.Sheets("CEO").Range("A1").Resize( _
.Rows.Count, .Columns.Count) = .Value
End With
wbSource.Close
' how to rewrite with a sub function so that I can call 10 times for A1, A2,B1,B2,C1,C2,D1,D2,E1,E2
' copy the spreadsheet from source to A1 tab, After that , if any cell in column A (ie. from A6) is a number greater than 0 ,sum up the related cell in column D and 'put the total in another spreadsheet on the same workbook fro statistic use
'A1
wbPath = ThisWorkbook.Sheets("Link").Range("B18").Value
Filename = Dir(wbPath, vbNormal)
If Filename <> "" Then
Set wbSource = Workbooks.Open(wbPath)
With wbSource.Sheets("Sheet1").UsedRange
'Now, paste to y worksheet:
wbTarget.Sheets("A1").Range("A1").Resize( _
.Rows.Count, .Columns.Count) = .Value
End With
wbSource.Close
lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 6 To lrow
If IsNumeric(Cells(i, 1)) And Cells(i, 1) > 0 Then
Cells(i, 10).Value = Cells(i, 4).Value
End If
Next i
End If