Making code more efficient!

T

tangychips

New Member
Joined
Feb 2, 2016
Messages
5
I made some code using the recorder and have attempted to tweak it. Finding it difficult to get any more gains from it as I am very new to all this (using the macro recorder was a huge step).

There is another macro very similar to this so if I could get help with this one, I could reverse engineer the changes and fix the other one myself. If someone could help thin out the code, I would be very gracious!



Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual




Sheets("Enter Data").Range("C3").Copy
Sheets("Products").Select
Range("B1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

ActiveCell.Offset(0, 1).Range("A1").Select
Sheets("Enter Data").Select
Range("C4").Select
Application.CutCopyMode = True
Selection.Copy
Sheets("Products").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

ActiveCell.Offset(0, 1).Range("A1").Select
Sheets("Enter Data").Select
Range("C5").Select
Application.CutCopyMode = True
Selection.Copy
Sheets("Products").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

ActiveCell.Offset(0, 1).Range("A1").Select
Sheets("Enter Data").Select
Range("C6").Select
Application.CutCopyMode = True
Selection.Copy
Sheets("Products").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

ActiveCell.Offset(0, 7).Range("A1").Select
Sheets("Enter Data").Select
Range("C7").Select
Application.CutCopyMode = True
Selection.Copy
Sheets("Products").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

ActiveCell.Offset(0, 1).Range("A1").Select
Sheets("Enter Data").Select
Range("C8").Select
Application.CutCopyMode = True
Selection.Copy
Sheets("Products").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

ActiveCell.Offset(0, 3).Range("A1").Select
Sheets("Enter Data").Select
Range("C9").Select
Application.CutCopyMode = True
Selection.Copy
Sheets("Products").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

ActiveCell.Offset(0, 2).Range("A1").Select
Sheets("Enter Data").Select
Range("E1").Select
Application.CutCopyMode = True
Selection.Copy
Sheets("Products").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

ActiveCell.Offset(0, 1).Range("A1").Select
Sheets("Enter Data").Select
Range("D1").Select
Application.CutCopyMode = True
Selection.Copy
Sheets("Products").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Dim rngData As Range, rngFormula As Range

With ThisWorkbook.Worksheets("Products")
Set rngData = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
Set rngFormula = .Range("F2:K2")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row, rngFormula.Column))
End With

With ThisWorkbook.Worksheets("Products")
Set rngData = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
Set rngFormula = .Range("N2:O2")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row, rngFormula.Column))
End With

With ThisWorkbook.Worksheets("Products")
Set rngData = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
Set rngFormula = .Range("Q2:Q3")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row, rngFormula.Column))
End With

Sheets("Enter Data").Select
Range("C3").Select




Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
What do you actually want to do with the code ??
 
Upvote 0
Welcome to the Forum!

It would help if you just explain what it is you are trying to do ....

For example, it looks like you're copying 'Enter Data'!C3:C10 to the first free row in Sheets("Products") column B. But it's not clear why you are doing this cell by cell? Also, will it always be C3:C10, or is it perhaps C3 down to the last populated cell in column C?
 
Upvote 0
Upvote 0
I'm trying to copy paste stuff from sheet "Enter Data" to "Products"

Sheet: "Enter Data"
93449947f1.png


into>sheet: "Products"

03aa49d236.png



The problem I am facing is that there are formulas in "Products" cells F:K, N:O and Q.

Is there a way to rearrange this to make it more efficient?
 
Upvote 0
StephenCrump said:
Welcome to the Forum!

It would help if you just explain what it is you are trying to do ....

For example, it looks like you're copying 'Enter Data'!C3:C10 to the first free row in Sheets("Products") column B. But it's not clear why you are doing this cell by cell? Also, will it always be C3:C10, or is it perhaps C3 down to the last populated cell in column C?
Click to expand...

I think I was doing it cell by cell due to my using the macro recorder.

The problem I am facing is that there are formulas within certain columns on the sheet that requires the pasting.

How do I best go about it?
 
Upvote 0
tangychips,

In your reply #5, you are posting pictures/graphics/PNGs. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get an answer.


You can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com
 
Upvote 0
Excel 2013
B​
C​
3​
Date Purchased1/1/1901
4​
IMEI00000000000000
5​
Batchtest
6​
SKUsamsunggalaxynote3_bl32c
7​
Clean IMEI?Y
8​
Total Cost
9​
Commentstest

<tbody>
</tbody>

Sheet: Enter Data

<tbody>
</tbody>

Excel 2013
Row\Col
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
O​
P​
Q​
R​
S​
1​
DateIMEIRefSKUBrandModelColour4G / WSizeConditionClean IMEITotalCostGSTCommentsStatusDate AddedInitials
2​
1/1/190100000000000000testtest=IFNA(VLOOKUP(RC5,'Mobile SKU Totals'!C3:C10,2, FALSE),"")=IFNA(VLOOKUP(RC5,'Mobile SKU Totals'!C3:C10,3, FALSE),"")=IFNA(VLOOKUP(RC5,'Mobile SKU Totals'!C3:C10,4, FALSE),"")=IFNA(IF(VLOOKUP(RC[-4],'Mobile SKU Totals'!C3:C10,5, FALSE)=0,"",VLOOKUP(RC[-4],'Mobile SKU Totals'!C3:C10,5, FALSE)),"")=IFNA(IF(VLOOKUP(RC[-5],'Mobile SKU Totals'!C3:C10,6, FALSE)=0,"",VLOOKUP(RC[-5],'Mobile SKU Totals'!C3:C10,6, FALSE)),"")=IFNA(IF(VLOOKUP(RC[-6],'Mobile SKU Totals'!C3:C10,7, FALSE)=0,"",VLOOKUP(RC[-6],'Mobile SKU Totals'!C3:C10,7, FALSE)),"")Y$0.10=IF(RC[-1]*10/11=0,"",RC[-1]*10/11)=IF(RC[-2]*1/11=0,"",RC[-2]*1/11)=IFNA(INDEX(Warranty!C[6],MATCH(Products!RC[3],WarrantyDateEdited,FALSE),1),"Available")

<tbody>
</tbody>
Sheet: Products

<tbody>
</tbody>
 
Upvote 0
tangychips

The macro recorder is a great way to get started, and useful for discovering the syntax for various tasks, but you'll quickly realise it records a lot of unnecessary steps. In particular, it's inefficient and rarely necessary to use .Select

Here's my quick take on one way you might do this.

I'm sure hiker95 will also give you a good VBA solution.

Code: 
Sub TransferData()

    Dim rngData As Range
    Dim lLastRow As Long
    
    Set rngData = Sheets("Data").Range("C3:C9")
    
    With Sheets("Products")
        lLastRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
        .Rows(lLastRow - 1).Copy .Rows(lLastRow)
        .Range("B" & lLastRow).Value = rngData(1).Value
        .Range("C" & lLastRow).Value = rngData(2).Value
        'etc etc
        .Range("P" & lLastRow).Value = rngData(7).Value
        .Range("R" & lLastRow).Value = Now
        .Range("S" & lLastRow).Value = "Your initials here"
     End With
     
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to loop a macro
Replies
2
Views
373
rlcurlee
R
P
  • Solved
Counter in For Each loop
Replies
2
Views
196
Possum17
P
K
  • Question
Please help me make this code more efficient!
Replies
3
Views
211
kevin9999
K
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
173
pennbowl
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
308
Azamamir
A

Forum statistics

Threads
1,214,944
Messages
6,122,384
Members
449,080
Latest member
Armadillos

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