Range("D1").End(xlDown).Offset(1).Select not working

Dean001

New Member
Joined
Sep 2, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I am very new to VBA and have got one macro working, but when I copy and paste it to make an almost identical one, it doesnt work, I want the cell info to be pasted in the next blank cell. This works on the CTRL+w shortcut macro, but not on the CTRL+g macro. would appreciate any help.
Thanks

This one works
Sub Auto_destination_wire()
'
' Auto_destination_wire Macro
' will take reading to wire table
'
' Keyboard Shortcut: Ctrl+w
'
Selection.Copy
Sheets("Graph Data Area 1").Select
Range("D1").End(xlDown).Offset(1).Select
ActiveSheet.Paste Link:=True
Sheets("data1").Select
Application.CutCopyMode = False
End Sub

This one doesnt work (doesnt drop down to paste in the next available row)
Sub Auto_gap()
'
' Auto_gap Macro
' will take reading to gap table
'
' Keyboard Shortcut: Ctrl+g
'
Selection.Copy
Sheets("Graph Data Area 1").Select
Range("I1").End(xlDown).Offset(1).Select
ActiveSheet.Paste Link:=True
Sheets("data1").Select
Application.CutCopyMode = False
End Sub

Thanks for any help you could offer.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
1) And where the data are copied, instead?
2) Has I1 any filled cell below it?
3) Are you sure that the blank cells below I1 are "empty" or do they contains formulas?
4) Are the macro in a "standard vba module"?
 
Upvote 0
1. The data is pasted to the same cell each time rather than dropping to the next one down

2. I1 has no filled cells below it

3. I have highlighted and deleted all cells below

4. Not sure what the standard one is. I recorded the macro, then went in and edited.

Thanks.
 
Upvote 0
1. The data is pasted to the same cell each time rather than dropping to the next one down
May we know the address of this "same cell"?

2. I1 has no filled cells below it
How this compare with column D?

Which type of information have been copied by Selection.Copy?
Also, if there is no any data under I1 then Range("I1").End(xlDown) whould point to the last available cell and .Offset(1).Select would result in an error; don't you receive an error message?

Anyway try using, instead of Range("I1").End(xlDown).Offset(1).Select
VBA Code:
Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).Select

4. Not sure what the standard one is. I recorded the macro, then went in and edited.
In that case it's a "standard module"
 
Upvote 0
Solution
You're a genius, that works perfectly, been trying to work it out the past 2 days. Thanks so much.
I obviously need to learn an awful lot. Any ideas where is best to learn more about VBA/macros/
Thanks
 
Upvote 0
Thank you for the feedback

Any ideas where is best to learn more about VBA/macros/
My knowledge come from experience, not from a specific training.
I started recording my macros then adding small modifications.
Microsoft' online help is very helpful in understanding the meaning of the instructions and the concepts of the language, as well as containing a wealth of examples.
The support you can get on this board will give you an additional boot
 
Upvote 0
it seems like it can save so much time and effort. Just wish I knew about it years ago.

I think I’ll try the same as that and just play around with recording and modifying as I go.

Thanks again for the help
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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