Hi,
Need help with VBA Code.
I've searched online and tried a few codes but have been having trouble finding something that works.
What I want to do is to insert "pastevalue" function in the below bold parts ( in order to import data with formulas in value from external files)
Below shows parts of the VBS input before the bold parts:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Appreciate your help!
Have a g'day!!
Need help with VBA Code.
I've searched online and tried a few codes but have been having trouble finding something that works.
What I want to do is to insert "pastevalue" function in the below bold parts ( in order to import data with formulas in value from external files)
VBA Code:
'Copying data from only used cells of Sheet1
wbOpen.Sheets("Trade Receivables").Activate
Set datalastcell = Cells(Rows.Count, "v").End(xlUp)
Set rngDatapl = Range("a2", datalastcell)
'Pasting the data of Sheet2 into the new workbook.
Lastrow = wsDataPL.Cells(Rows.Count, "a").End(xlUp).Row
rngDatapl.Copy Destination:=wsDataPL.Cells(Lastrow + 1, 1)Below shows parts of the VBS input before the bold parts:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
VBA Code:
Sub ImportData()
Dim rngFullName As Range
Dim wbOpen As Workbook
Dim wsOpenPL As Worksheet
Dim wsOpenPC As Worksheet
Dim wsOpenOI As Worksheet
Dim wsOpenOE As Worksheet
Dim wsOpenSt As Worksheet
Dim wsOpenSk As Worksheet
Dim wsOpenLt As Worksheet
Dim wsOpenLf As Worksheet
Dim wsOpenAP As Worksheet
Dim wsOpenNR As Worksheet
Dim wsOpenAR As Worksheet
Dim wsOpenAM As Worksheet
Dim wsOpenIN As Worksheet
Dim wsOpenPR As Worksheet
Dim wbData As Workbook
Dim wsDataCtrl As Worksheet
Dim wsDataMP As Worksheet
Dim wsDataPL As Worksheet
Dim wsDataPC As Worksheet
Dim wsDataOI As Worksheet
Dim wsDataOE As Worksheet
Dim wsDataSt As Worksheet
Dim wsDataSk As Worksheet
Dim wsDataLt As Worksheet
Dim wsDataLf As Worksheet
Dim wsDataAp As Worksheet
Dim wsDataNR As Worksheet
Dim wsDataAR As Worksheet
Dim wsDataAM As Worksheet
Dim wsDataIN As Worksheet
Dim wsDataPR As Worksheet
Dim rngDatapl As Range
Dim rngDataPC As Range
Dim rngDataOI As Range
Dim rngDataOE As Range
Dim rngDataSt As Range
Dim rngDataSk As Range
Dim rngDataLt As Range
Dim rngDataLf As Range
Dim rngDataAp As Range
Dim rngDataNR As Range
Dim rngDataAR As Range
Dim rngDataAM As Range
Dim rngDataIN As Range
Dim rngDataPR As Range
Dim datalastcellpl As Range
Dim Lastrow As Long
Dim datalastcell As Range
Dim ConsolLastRowpc As Long
Dim strControl As String
Dim lngNext As Long
Application.ScreenUpdating = False
strControl = "Failed"
'setting objects on ThisWorkbook and ThisWorkbook.ActiveSheet
Set wbData = ThisWorkbook
Set wsDataCtrl = wbData.Sheets("Control")
Set wsDataMP = wbData.Sheets("Main_Page")
Set wsDataPL = wbData.Sheets("Trade Receivables")
Set wsDataPC = wbData.Sheets("Other receivables")
Set wsDataOI = wbData.Sheets("Trade Payables")
Set wsDataOE = wbData.Sheets("Other Payables")
Set wsDataSt = wbData.Sheets("Stk out no inv")
Set wsDataSk = wbData.Sheets("Stock Aging")
Set wsDataAp = wbData.Sheets("AP accrual")
Set wsDataLt = wbData.Sheets("Loan to")
Set wsDataLf = wbData.Sheets("Loan from")
Set wsDataNR = wbData.Sheets("Note Receivables")
Set wsDataAR = wbData.Sheets("Advance receipt")
Set wsDataAM = wbData.Sheets("Advance payment")
Set wsDataIN = wbData.Sheets("Inv but not stk out")
Set wsDataPR = wbData.Sheets("Prov. for doubtful debts")
Set rngFullName = wsDataCtrl.Range("H5")
'if the workbboks always are located in the same directory, use
' ThisWorkbook.Path & Application.PathSeparator & Filename & ".xlsm"
On Error GoTo ErrorRoutine
'open workbook with data and setting objects to both Workbook and Worksheet
Set wbOpen = Workbooks.Open(rngFullName & ".xlsm", UpdateLinks:=False)
Set wsOpenPL = wbOpen.Sheets("Trade Receivables")
Set wsOpenPC = wbOpen.Sheets("Other receivables")
Set wsOpenOI = wbOpen.Sheets("Trade Payables")
Set wsOpenOE = wbOpen.Sheets("Other Payables")
Set wsOpenSt = wbOpen.Sheets("Stk out no inv")
Set wsOpenSk = wbOpen.Sheets("Stock Aging")
Set wsOpenLt = wbOpen.Sheets("Loan to")
Set wsOpenLf = wbOpen.Sheets("Loan from")
Set wsOpenAP = wbOpen.Sheets("AP accrual")
Set wsOpenNR = wbOpen.Sheets("Note Receivables")
Set wsOpenAR = wbOpen.Sheets("Advance receipt")
Set wsOpenAM = wbOpen.Sheets("Advance payment")
Set wsOpenIN = wbOpen.Sheets("Inv but not stk out")
Set wsOpenPR = wbOpen.Sheets("Prov. for doubtful debts")
'Copying data from only used cells of Sheet1
wbOpen.Sheets("Trade Receivables").Activate
Set datalastcell = Cells(Rows.Count, "v").End(xlUp)
Set rngDatapl = Range("a2", datalastcell)
'Pasting the data of Sheet2 into the new workbook.
Lastrow = wsDataPL.Cells(Rows.Count, "a").End(xlUp).Row
rngDatapl.Copy Destination:=wsDataPL.Cells(Lastrow + 1, 1)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Appreciate your help!
Have a g'day!!
Last edited by a moderator:
