Making code more efficient!

tangychips · Feb 2, 2016

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

Michael M · Feb 2, 2016

What do you actually want to do with the code ??

StephenCrump · Feb 2, 2016

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?

hiker95 · Feb 2, 2016

tangychips,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

tangychips · Feb 2, 2016

I'm trying to copy paste stuff from sheet "Enter Data" to "Products"

Sheet: "Enter Data"

into>sheet: "Products"

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?

tangychips · Feb 2, 2016

hiker95 said:
tangychips,
1. What version of Excel, and, Windows are you using?
2. Are you using a PC or a Mac?

1. What version of Excel, and, Windows are you using?
Office 2013
2. Are you using a PC or a Mac?
PC

tangychips · Feb 2, 2016

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?

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?

hiker95 · Feb 2, 2016

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

tangychips · Feb 2, 2016

Excel 2013
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

B

[/TH]
[TH]

C

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3

[/TD]
[TD="bgcolor: #DDEBF7"]Date Purchased[/TD]
[TD="bgcolor: #FFFFFF"]1/1/1901[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4

[/TD]
[TD="bgcolor: #DDEBF7"]IMEI[/TD]
[TD]00000000000000[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5

[/TD]
[TD="bgcolor: #DDEBF7"]Batch[/TD]
[TD="bgcolor: #FFFFFF"]test[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6

[/TD]
[TD="bgcolor: #DDEBF7"]SKU[/TD]
[TD="bgcolor: #FFFFFF"]samsunggalaxynote3_bl32c[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7

[/TD]
[TD="bgcolor: #DDEBF7"]Clean IMEI?[/TD]
[TD]Y[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8

[/TD]
[TD="bgcolor: #DDEBF7"]Total Cost[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9

[/TD]
[TD="bgcolor: #DDEBF7"]Comments[/TD]
[TD]test[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Enter Data[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2013
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]_Row\^Col[/TH]
[TH]

B

[/TH]
[TH]

C

[/TH]
[TH]

D

[/TH]
[TH]

E

[/TH]
[TH]

F

[/TH]
[TH]

G

[/TH]
[TH]

H

[/TH]
[TH]

I

[/TH]
[TH]

J

[/TH]
[TH]

K

[/TH]
[TH]

L

[/TH]
[TH]

M

[/TH]
[TH]

N

[/TH]
[TH]

O

[/TH]
[TH]

P

[/TH]
[TH]

Q

[/TH]
[TH]

R

[/TH]
[TH]

S

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1

[/TD]
[TD="bgcolor: #DDEBF7"]Date[/TD]
[TD="bgcolor: #DDEBF7"]IMEI[/TD]
[TD="bgcolor: #DDEBF7"]Ref[/TD]
[TD="bgcolor: #DDEBF7"]SKU[/TD]
[TD="bgcolor: #DDEBF7"]Brand[/TD]
[TD="bgcolor: #DDEBF7"]Model[/TD]
[TD="bgcolor: #DDEBF7"]Colour[/TD]
[TD="bgcolor: #DDEBF7"]4G / W[/TD]
[TD="bgcolor: #DDEBF7"]Size[/TD]
[TD="bgcolor: #DDEBF7"]Condition[/TD]
[TD="bgcolor: #DDEBF7"]Clean IMEI[/TD]
[TD="bgcolor: #DDEBF7"]Total[/TD]
[TD="bgcolor: #DDEBF7"]Cost[/TD]
[TD="bgcolor: #DDEBF7"]GST[/TD]
[TD="bgcolor: #DDEBF7"]Comments[/TD]
[TD="bgcolor: #DDEBF7"]Status[/TD]
[TD="bgcolor: #E2EFDA"]Date Added[/TD]
[TD="bgcolor: #E2EFDA"]Initials[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2

[/TD]
[TD]1/1/1901[/TD]
[TD]00000000000000[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]=IFNA(VLOOKUP(RC5,'Mobile SKU Totals'!C3:C10,2, FALSE),"")[/TD]
[TD]=IFNA(VLOOKUP(RC5,'Mobile SKU Totals'!C3:C10,3, FALSE),"")[/TD]
[TD]=IFNA(VLOOKUP(RC5,'Mobile SKU Totals'!C3:C10,4, FALSE),"")[/TD]
[TD]=IFNA(IF(VLOOKUP(RC[-4],'Mobile SKU Totals'!C3:C10,5, FALSE)=0,"",VLOOKUP(RC[-4],'Mobile SKU Totals'!C3:C10,5, FALSE)),"")[/TD]
[TD]=IFNA(IF(VLOOKUP(RC[-5],'Mobile SKU Totals'!C3:C10,6, FALSE)=0,"",VLOOKUP(RC[-5],'Mobile SKU Totals'!C3:C10,6, FALSE)),"")[/TD]
[TD]=IFNA(IF(VLOOKUP(RC[-6],'Mobile SKU Totals'!C3:C10,7, FALSE)=0,"",VLOOKUP(RC[-6],'Mobile SKU Totals'!C3:C10,7, FALSE)),"")[/TD]
[TD]Y[/TD]
[TD]$0.10[/TD]
[TD]=IF(RC[-1]*10/11=0,"",RC[-1]*10/11)[/TD]
[TD]=IF(RC[-2]*1/11=0,"",RC[-2]*1/11)[/TD]
[TD][/TD]
[TD="bgcolor: #FF3300"]=IFNA(INDEX(Warranty!C[6],MATCH(Products!RC[3],WarrantyDateEdited,FALSE),1),"Available")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Products[/TD]
[/TR]
</tbody>[/TABLE]

StephenCrump · Feb 2, 2016

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

Making code more efficient!

tangychips

New Member

Michael M

Well-known Member

StephenCrump

MrExcel MVP

hiker95

Well-known Member

tangychips

New Member

tangychips

New Member

tangychips

New Member

hiker95

Well-known Member

tangychips

New Member

StephenCrump

MrExcel MVP

Similar threads

Share this page

Making code more efficient!

New Member

Well-known Member

MrExcel MVP

Well-known Member

New Member

New Member

New Member

Well-known Member

New Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock