Search code examples
vbaexcelcopy-paste

Dynamic Copy and Special Paste

I download a report which will always comes columns A:AH, I also always need to re-format it by Special Paste > Multiply by 1 to use formulas. It can vary between 500 and 25000 rows.

I can manually enter "1" into cell D1.

From another post, I have the following code which copies and pastes.

When I paste the data into Sheet2, I want it to be Special Paste Multiplied by 1.

Sub DynaCopyPaste()

Application.ScreenUpdating = False

Dim s1, s2 As Excel.Worksheet
Dim iLastCellS2 As Excel.Range
Dim iLastRowS1 As Long

Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")

'get last row
iLastRowS1 = s1.Cells(s1.Rows.Count, "AH").End(xlUp).Row

'get last available cell to paste
Set iLastCellS2 = s2.Cells(s2.Rows.Count, "A").End(xlUp).Offset(1, 0)

'copy and paste into Sheet2
s1.Range("A1", s1.Cells(iLastRowS1, "AH")).Copy
iLastCellS2.PasteSpecial xlPasteValues

Application.ScreenUpdating = True

enter image description here

I have added a screenshot of my problem. I cannot apply excel functions to the data - please see the function in G11.

What currently works:

  1. Type "1" into an empty cell

  2. Copy that cell to the clipboard

  3. Select all data

  4. Paste > Special Paste > Multiply by

My goal is to build an analytics package to manipulate the data, my first step in the code is formatting it so that I can write functions onto the page.

Solution

  • I think you can do this without actually copying or pasting anything.

    Dim rngToCopy as Range
'copy and paste into Sheet2
Set rngToCopy = s1.Range("A1", s1.Cells(iLastRowS1, "AH"))
iLastCellS2.Resize(rngToCopy.Rows.Count, rngToCopy.Columns.Count).Value = rngToCopy.Value

    If that doesn't work, then I think this should:

    Dim multiplier as Range
Set multiplier = s1.Range("D1")  '## This is the cell containing your value of 1

Dim rngToCopy as Range, destRange as Range
'## put the values in s2 worksheet:
Set rngToCopy = s1.Range("A1", s1.Cells(iLastRowS1, "AH"))
Set destRange = iLastCellS2.Resize(rngToCopy.Rows.Count, rngToCopy.Columns.Count)
destRange.Value = rngToCopy.Value

multiplier.Copy
destRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _ 
    SkipBlanks:=False, Transpose:=False