Copying Data from one sheet to another 'error 1004

K

karlm98

New Member
Joined
Jul 4, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hey Guys,

I tried to copy data from one sheet to another due to a commandbutton. If i ran the program on the target sheet, the error message 1004 appears, although running the code on the source sheet, everthing works fine. Can someone help me with that?


1657608504158.png
1657608529036.png


This id the Code that i use
Private Sub CopyCurrencyRates()


Dim Answer As VbMsgBoxResult
Dim NextFreeRow As Long
Dim LastUpdatedMonth As Long
Dim LastRow As Long
Dim Starcell As Range
Dim LastCell As Long


LastUpdatedMonth = Format(Date, "m") + 5 '+4 Wegen der zusätzlich eingefügten Spalten

LastCell = Worksheets("Data_Source").UsedRange.Rows.Count

NextFreeRow = Worksheets("Target Sheet").UsedRange.Rows.Count + 1

LastRow = Worksheets("Target Sheet").UsedRange.Rows.Count

StartCell = Worksheets("Data_Source").Cells(4, LastUpdatedMonth).Select

Answer = MsgBox("Are you sure to add the data for" & " " & MonthName(LastUpdatedMonth - 6) & " " & "to the Target Sheet?", vbYesNoCancel, "Add Data to Target-Sheet")

If Answer = vbYes Then

'HMA

'Get Data
Worksheets("Data_Source").Range(Cells(4, LastUpdatedMonth), Cells(LastCell, LastUpdatedMonth)).Copy
Worksheets("Target Sheet").Range("F" & NextFreeRow).PasteSpecial

'Get Currency
Worksheets("Data_Source").Range(Cells(4, 1), Cells(LastCell, 1)).Copy
Worksheets("Target Sheet").Range("C" & NextFreeRow).PasteSpecial

'Get Cl.
Worksheets("Data_Source").Range(Cells(4, 2), Cells(LastCell, 2)).Copy
Worksheets("Target Sheet").Range("A" & NextFreeRow).PasteSpecial

'get Exc.Currency
Worksheets("Data_Source").Range(Cells(4, 3), Cells(LastCell, 3)).Copy
Worksheets("Target Sheet").Range("D" & NextFreeRow).PasteSpecial

'get ExchangeRate
Worksheets("Data_Source").Range(Cells(4, 4), Cells(LastCell, 4)).Copy
Worksheets("Target Sheet").Range("B" & NextFreeRow).PasteSpecial

'Get Date
Worksheets("Data_Source").Range(Cells(4, 5), Cells(LastCell, 5)).Copy
Worksheets("Target Sheet").Range("E" & NextFreeRow).PasteSpecial

'get RF
Worksheets("Data_Source").Range(Cells(4, 6), Cells(LastCell, 6)).Copy
Worksheets("Target Sheet").Range("G" & NextFreeRow).PasteSpecial
Worksheets("Target Sheet").Range("H" & NextFreeRow).PasteSpecial
End If
End Sub
 

Attachments

  • 1657608458927.png
    1657608458927.png
    38.9 KB · Views: 4

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
In both images, in the body and in the attachment, the actual tabs name are "Data" and "Target", not "Data_Source" and "Target Sheet" as displayed in code.

Is this the reason?
 
Upvote 0
Hey bebo Thanks a lot, that was my mistake but i have already fixed it, although its not the problem ... This error appears now

1657614360390.png
 
Upvote 0
COuld you post both sheets, via XL2BB?
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
TestExcel BTC.xlsm
ABCDEFGHIJKLMNO
1Cl.ExRtCurrencyFromCurrencyToValidFromExchangeRateRFRT
2001EURBitcoin (BTC) EUR2022.01.31/3,672611
3001EUREthereum (ETH) EUR2022.01.31/1,785711
4001EURTether (USDT) EUR2022.01.31/103,8356911
5001EURUSD Coin (USDC) EUR2022.01.31/1,3927611
6001EURBinance Coin (BNB)EUR2022.01.31/1,72711
7001EURBinance USD (BUSD) EUR2022.01.31/6,8166811
8001EURXRP (XRP) EUR2022.01.31/5,5368211
9001EURCardanoEUR2022.01.31/1,2617311
10001EURIotaEUR2022.01.32/1,2617411
11001EURIDHEUR2022.01.33/1,2617511
12001USDBitcoin (BTC) USD2022.01.31/3,672611
13001USDEthereum (ETH) USD2022.01.31/1,785711
14001USDTether (USDT) USD2022.01.31/103,8356911
15001USDUSD Coin (USDC) USD2022.01.31/1,3927611
16001USDBinance Coin (BNB)USD2022.01.31/1,72711
17001USDBinance USD (BUSD) USD2022.01.31/6,8166811
18001USDXRP (XRP) USD2022.01.31/5,5368211
19001USDCardanoUSD2022.01.31/1,2617311
20001USDIotaUSD2022.01.32/1,2617411
21001USDIDHUSD2022.01.33/1,2617511
Target



TestExcel BTC.xlsm
ABCDEFGHIJKLMNOPQR
2HMAHMAHMAHMAHMAHMAHMAHMAHMAHMAHMAHMA
3JanFebMarAprMayJunJulAugSepOctNovDec
4Bitcoin (BTC) 001EURHMA30.06.20221/3,6726/3,6727/3,6728/3,6729/3,6731/3,6730 EUR
5Ethereum (ETH) 001EURHMA30.06.20221/1,7857/1,7858/1,7859/1,7860/1,7861/1,7862
6Tether (USDT) 001EURHMA30.06.20221/103,83569/103,83570/103,83571/103,83572/103,83573/103,83574
7USD Coin (USDC) 001EURHMA30.06.20221/1,39276/1,39276/1,39276/1,39276/1,39276/1,39276
8Binance Coin (BNB)001EURHMA30.06.20221/1,727/1,727/1,727/1,727/1,727/1,727
9Binance USD (BUSD) 001EURHMA30.06.20221/6,81668/6,81669/6,81670/6,81671/6,81672/6,81673
10XRP (XRP) 001EURHMA30.06.20221/5,53682/5,53683/5,53684/5,53685/5,53686/5,53687
11Cardano001EURHMA30.06.20221/1,26173/1,26173/1,26173/1,26173/1,26173/1,26173
12Iota001EURHMA30.06.20221/1,26174/1,26174/1,26174/1,26174/1,26174/1,26174
13IDH001EURHMA30.06.20221/1,26175/1,26175/1,26175/1,26175/1,26175/1,26175
Source
Cell Formulas
RangeFormula
E4:E13E4=EOMONTH(TODAY(),-1)
 
Upvote 0
i see now, that the button is not pasted, how can i do that?
 
Upvote 0
No need the button, but the code.
Right-click on the button/ assign code, then copy the code
(SInce I need the latest code, after fixed)
 
Upvote 0
Hey Bebo,
I've fixed the problem by defining the source sheet as activesheet. VBA did not know the right destination and can only copy out of the activesheet.

Thank you for your help!

kind regards
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
277
birdman15
B
M
  • Solved
Macro moving cells from sheet to sheet
Replies
3
Views
421
SimpleDAS
S
D
  • Solved
Copy row based on cell value and paste as text in another worksheet
Replies
2
Views
566
DH937
D
R
  • Question
Loop through Range and Force Formula Calculation
Replies
0
Views
187
reberryjr
R
Keebler
  • Solved
new project: copy range without empty or blank rows
5 6 7
Replies
68
Views
1K
Keebler
Keebler

Forum statistics

Threads
1,215,744
Messages
6,126,623
Members
449,322
Latest member
Ricardo Souza

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top