spaceranger
11-07-2012, 06:03 AM
Hi all,
Long time reader - first time writer. :-)
I'm trying to copy some specific cells (only numbers, no text) from one worksheet in one workbook to one sheet in another workbook.
It's been two years since I last used VBA, so I am a little rusty.
The source numbers are several digits, but I need them rounded to zero decimals and to the nearest thousand.
The first line of data copying is different from the others below because I tried to simplify it to get the whole thing working.
When I get to the bold line below, I get the error message: "Runtime error '5': invalid procedure call or argument."
Can you please help me out..? :hi:
I'm sorry if some of the help text below doesn't make any sense - I got a bit frustrated in the end and tried several solutions........
I got the basic idea to the code from an online example. That's where the term "customer" comes from, but I went with it to start out.
For those of you interested in the purpose of all of this: I work for a broker and I'm responsible for submitting the key numbers to the Danish government, so my boss doesn't lose his license to sell. All the numbers from our books go in a standard excel form provided by the governments financial department.
Here goes:
Option Explicit
Public Sub FillOut_Target()
' Get customer workbook...
Dim filter As String
Dim caption As String
Dim targetFilename As String
Dim customerFilename As String
'Dim customerBook As Workbook
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim targetSheet As Worksheet
Dim sourceSheet As Worksheet
'pop op boks
'MsgBox ("About if not ES-file is active")
targetFilename = Application.GetOpenFilename(, , "TARGET")
Set targetWorkbook = Application.Workbooks.Open(targetFilename) 'Application.ActiveWorkbook
' get the customer workbook
'filter = "*.xlsx, *.xls"
customerFilename = Application.GetOpenFilename(, , "SOURCE") 'filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
'''' copy data from customer to target workbook -----------------------
'choose target sheet #2
Set targetSheet = targetWorkbook.Worksheets(2)
'chosse source sheet #5
Set sourceSheet = customerWorkbook.Worksheets(5)
' F3 is copied from source sheet #5 to target sheet #2 cell C8:
targetSheet.Cells("F3").Value = sourceSheet.Cells("C8").Value
' andre celler kopieres til ES01:
targetSheet.Cells("E3").Value = Round(sourceSheet.Cells("E8").Value / 1000, 0)
targetSheet.Cells("F17").Value = Round(sourceSheet.Cells("C11").Value / 1000, 0)
targetSheet.Cells("e17").Value = Round(sourceSheet.Cells("e11").Value / 1000, 0)
targetSheet.Cells("F18").Value = Round(sourceSheet.Cells("C12").Value / 1000, 0)
targetSheet.Cells("e18").Value = Round(sourceSheet.Cells("e12").Value / 1000, 0)
targetSheet.Cells("F19").Value = Round(sourceSheet.Cells("C13").Value / 1000, 0)
targetSheet.Cells("e19").Value = Round(sourceSheet.Cells("e13").Value / 1000, 0)
targetSheet.Cells("F26").Value = Round(sourceSheet.Cells("C15").Value / 1000, 0)
targetSheet.Cells("e26").Value = Round(sourceSheet.Cells("e15").Value / 1000, 0)
targetSheet.Cells("F36").Value = Round(sourceSheet.Cells("C17").Value * (-1) / 1000, 0)
targetSheet.Cells("e36").Value = Round(sourceSheet.Cells("e17").Value * (-1) / 1000, 0)
targetSheet.Cells("F42").Value = Round(sourceSheet.Cells("C19").Value * (-1) / 1000, 0)
targetSheet.Cells("e42").Value = Round(sourceSheet.Cells("e19").Value * (-1) / 1000, 0)
'''' end copying ------------------------------------------------------
' Close customer workbook
customerWorkbook.Close
End Sub
THANKS!
Long time reader - first time writer. :-)
I'm trying to copy some specific cells (only numbers, no text) from one worksheet in one workbook to one sheet in another workbook.
It's been two years since I last used VBA, so I am a little rusty.
The source numbers are several digits, but I need them rounded to zero decimals and to the nearest thousand.
The first line of data copying is different from the others below because I tried to simplify it to get the whole thing working.
When I get to the bold line below, I get the error message: "Runtime error '5': invalid procedure call or argument."
Can you please help me out..? :hi:
I'm sorry if some of the help text below doesn't make any sense - I got a bit frustrated in the end and tried several solutions........
I got the basic idea to the code from an online example. That's where the term "customer" comes from, but I went with it to start out.
For those of you interested in the purpose of all of this: I work for a broker and I'm responsible for submitting the key numbers to the Danish government, so my boss doesn't lose his license to sell. All the numbers from our books go in a standard excel form provided by the governments financial department.
Here goes:
Option Explicit
Public Sub FillOut_Target()
' Get customer workbook...
Dim filter As String
Dim caption As String
Dim targetFilename As String
Dim customerFilename As String
'Dim customerBook As Workbook
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim targetSheet As Worksheet
Dim sourceSheet As Worksheet
'pop op boks
'MsgBox ("About if not ES-file is active")
targetFilename = Application.GetOpenFilename(, , "TARGET")
Set targetWorkbook = Application.Workbooks.Open(targetFilename) 'Application.ActiveWorkbook
' get the customer workbook
'filter = "*.xlsx, *.xls"
customerFilename = Application.GetOpenFilename(, , "SOURCE") 'filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
'''' copy data from customer to target workbook -----------------------
'choose target sheet #2
Set targetSheet = targetWorkbook.Worksheets(2)
'chosse source sheet #5
Set sourceSheet = customerWorkbook.Worksheets(5)
' F3 is copied from source sheet #5 to target sheet #2 cell C8:
targetSheet.Cells("F3").Value = sourceSheet.Cells("C8").Value
' andre celler kopieres til ES01:
targetSheet.Cells("E3").Value = Round(sourceSheet.Cells("E8").Value / 1000, 0)
targetSheet.Cells("F17").Value = Round(sourceSheet.Cells("C11").Value / 1000, 0)
targetSheet.Cells("e17").Value = Round(sourceSheet.Cells("e11").Value / 1000, 0)
targetSheet.Cells("F18").Value = Round(sourceSheet.Cells("C12").Value / 1000, 0)
targetSheet.Cells("e18").Value = Round(sourceSheet.Cells("e12").Value / 1000, 0)
targetSheet.Cells("F19").Value = Round(sourceSheet.Cells("C13").Value / 1000, 0)
targetSheet.Cells("e19").Value = Round(sourceSheet.Cells("e13").Value / 1000, 0)
targetSheet.Cells("F26").Value = Round(sourceSheet.Cells("C15").Value / 1000, 0)
targetSheet.Cells("e26").Value = Round(sourceSheet.Cells("e15").Value / 1000, 0)
targetSheet.Cells("F36").Value = Round(sourceSheet.Cells("C17").Value * (-1) / 1000, 0)
targetSheet.Cells("e36").Value = Round(sourceSheet.Cells("e17").Value * (-1) / 1000, 0)
targetSheet.Cells("F42").Value = Round(sourceSheet.Cells("C19").Value * (-1) / 1000, 0)
targetSheet.Cells("e42").Value = Round(sourceSheet.Cells("e19").Value * (-1) / 1000, 0)
'''' end copying ------------------------------------------------------
' Close customer workbook
customerWorkbook.Close
End Sub
THANKS!