Oct 29 2020 04:49 AM
Hi,
Requirement :
I have one excel file say Excel-A which having one WorkSheet Sate WS-1 placed at my Window Folder and i want to copy data of WS-1 to another Excel-2 using Macro.
I want to write Macro in a New Excel Sheet and will call Source file Excel - A from there and then need to filter data as well and then finally paste to Excel-2 using Macro.
Please suggest!!
Thank
Amit
Oct 29 2020 10:43 AM
Here is a small solution, maybe you can adapt it to your needs.
Untested
Private Function GetValue (path As String, file As String, sheet As String, reference As String)
Dim Argument As String
'Add backslash if not available ...
If Right (Path, 1) <> "\" Then
Path = path & "\"
End If
'Check the existence of the file ...
If Dir (path & file) = "" Then
GetValue = "File not found"
Exit function
End If
'Generate argument ...
Argument = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range (reference) .Range ("A1"). Address (,, xlUp)
'Read out ...
GetValue = ExecuteExcel4Macro (argument)
End function
Public Sub GetValue_2020 ()
Dim path As String
Dim file As String
Dim table As String
Dim Cell As Range
Dim Map As Worksheet
Dim area As String
'Set a reference to the folder ...
Set folder = worksheets ("annual plan")
'Initialize ...
Path = "W: \ Z042 \ 042-Absences \"
File = "2020.xls"
Table = "annual plan"
Range = "E9: H391"
'Loop through all cells ...
For Each Cell In Book.Range (range)
'Read value ...
Cell.Value = GetValue (Path, File, Table, Cell.Address)
'Do not accept null values
If IsNumeric (Cell.Value) Then
If Cell.Value = 0 Then
Cell.ClearContents
End If
End If
Next cell
' To clean up...
Set wks = Nothing
End Sub
Hope I was able to help you.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here