VBA copy cells from one book to another

[spaceranger]

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!

[Trebor76]

Hi spaceranger,

Welcome to the forum!!

The cells method requires a row and column argument i.e for the bolded line you'd use this:


targetSheet.Cells(3, "F").Value = sourceSheet.Cells(8,"C").Value

If you want to reference cells as per you posted example, you need to use the Range argument, i.e.


targetSheet.Range("F3").Value = sourceSheet.Range("C8").Value

HTH

Robert

[spaceranger]

Hi spaceranger,

Welcome to the forum!!

The cells method requires a row and column argument i.e for the bolded line you'd use this:


targetSheet.Cells(3, "F").Value = sourceSheet.Cells(8,"C").Value

If you want to reference cells as per you posted example, you need to use the Range argument, i.e.


targetSheet.Range("F3").Value = sourceSheet.Range("C8").Value

HTH

Robert


THANK YOU, Robert!!

It all comes back to me now, thank you so much. With this I'll be able to cut 10% off my work load.

[Trebor76]

Thanks for letting us know and you're welcome :)

[spaceranger]

Thanks for letting us know and you're welcome :)

Robert,

I tried to buy the Macro introduction pdf-file from your website, but after paying via Paypal nothing really happend. I did recieve a payment confirmation from Paypal.

Introduction to Macros User Guide
Item# 1150131

Can you help me out?

Thanks

[Trebor76]

Ah, so you're the one :)

I'll send you the document shortly. Thanks for buying it and I hope you find it useful.

Kind regards,

Robert