Solved (VBA) Move selected cell one to the LEFT

August 9, 2019 at 02:06:33
Specs: Windows 10
Hi,

I created a worksheet in excel to calculate my offers to my customers. Each offer is then SAVED into a separate database (new excel sheet) , one row per offer.

The strategy I use is that I simply link the values from my worksheet into the very first row of my database (A1:Q1)

Whereafter I run the following code :
Sheets("Quotes").Range("A1:Q1").Select
Selection.Copy
Sheets("Quotes").Range("B1").End(xlDown).Offset(1, 0).Select
-----------------
note: .Range("B1") has to be B1 for a specific reason. I know I can fix it by changing the value to A1, but that is not what I need.
The above code searches for the first EMPTY row in the database so it can paste the new offer in an empty row.
However, I need to first move one cell te the left --> A# because my current selection is on the B# column. Otherwise my offer is pasted incorrectly and doesnt align with the others.
- I also understand I need to enter the correct code in this area, hence why I am typing in between the code.
-----------------
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks("Quotes.xlsm").Close Savechanges:=True
Workbooks("Skye.xlsm").Activate

I hope I explained it correctly and thanks to help me out.

Michael



See More: (VBA) Move selected cell one to the LEFT

Reply ↓  Report •

#1
August 9, 2019 at 06:14:59
✔ Best Answer
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. In the future, please post your code using the pre tags as shown.

Second, you have a lot a Select instructions in your code. Rarely do you need to actually Select an object in order perform an operation on it. In the vast majority of cases, you can directly reference the object within VBA and perform the operation, typically in a single instruction. "Physically" selecting objects in VBA is very inefficient. Yes, the Macro Recorder will always write the instructions using .Select, but it's your job to edit the code afterwards to make it more efficient.

Your code is a perfect example of why Selecting an object is very inefficient. You aren't actually using the cell selected by the following instruction, you just need to know what Row that cell is in.

Sheets("Quotes").Range("B1").End(xlDown).Offset(1, 0).Select

Once you know that Row number, you can paste your data into that Row, starting in Column A.

Take a look at this code and let me know what you think.

Sub PasteRow1()
Dim nxtRw As Long

'Determine next empty row in Column B
     nxtRw = Sheets("Quotes").Range("B1").End(xlDown).Offset(1, 0).Row
     
'Copy/Paste data
     Sheets("Quotes").Range("A1:Q1").Copy
     Sheets("Quotes").Range("A" & nxtRw).PasteSpecial Paste:=xlPasteValues

'Save, Close, Activate
     Workbooks("Quotes.xlsm").Close Savechanges:=True
     Workbooks("Skye.xlsm").Activate
     
End Sub

One more item. Since you are referencing Sheets("Quotes") in contiguous instructions, you can reference it once by using the With and End With statements to make the code a little easier to follow:


Sub PasteRow1()
Dim nxtRw As Long

  With Sheets("Quotes")

'Determine next empty row in Column B
     nxtRw = .Range("B1").End(xlDown).Offset(1, 0).Row
     
'Copy/Paste data
     .Range("A1:Q1").Copy
     .Range("A" & nxtRw).PasteSpecial Paste:=xlPasteValues
     
  End With

'Save, Close, Activate
     Workbooks("Quotes.xlsm").Close Savechanges:=True
     Workbooks("Skye.xlsm").Activate
     
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
August 16, 2019 at 04:38:47
Thannnnk you!
Seems to work!

Reply ↓  Report •
Related Solutions


Ask Question