VBA Copy and paste into the next column

[Mykiej23]

Hi

I'm trying to create a macro to copy and paste values in C2-45 into another spreadsheet in the next available column.

At the moment Column A has our description, and then column B would have the first dates data. Column C should have a sum to compare the difference from today to yesterday to highlight increases/decreases. And then this pattern continues on, D data entry and E difference formula.

Right now we download a report which is imported and formulas in column C of the Data Entry tab to calculate this. I want a create a macro button just to enter this data into the next free column started at B2. (Row 1 has headers). Is this possible? I've tried multiple different scenarios but I cant get it right.

This is what I have so far:

Sub DataEntry()
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Price Band Integrity 2019").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Select
End Sub

 

[vcoolio]

Hello Mykiej23,

Assuming that you're always working with the fixed range (C2:C45),try the following:-

Sub GoToNextCol()

Application.ScreenUpdating = False

      Dim NextCol As Long
      NextCol = Sheet2.Cells(2, Columns.Count).End(xlToLeft).Column + 1

      Sheet1.Range("C2:C45").Copy
      Sheet2.Cells(2, NextCol).PasteSpecial xlValues

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

You may have to change the sheet names to suit. I've used sheet codes in the above macro.

I hope that this helps.

Cheerio,
vcoolio.

 

[Mykiej23]

Hello Mykiej23,

Assuming that you're always working with the fixed range (C2:C45),try the following:-

Sub GoToNextCol()

Application.ScreenUpdating = False

      Dim NextCol As Long
      NextCol = Sheet2.Cells(2, Columns.Count).End(xlToLeft).Column + 1

      Sheet1.Range("C2:C45").Copy
      Sheet2.Cells(2, NextCol).PasteSpecial xlValues

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

You may have to change the sheet names to suit. I've used sheet codes in the above macro.

I hope that this helps.

Cheerio,
vcoolio.

Thanks! I've tried this and it runs with no errors but doesnt seem to input anything I've tried deleting all the current data out to start fresh but still nothing.

 

[vcoolio]

Did you check the sheet names?

Cheerio,
vcoolio.

 

[vcoolio]

..................and here's the link to a sample file which shows how the code works. Click on the "RUN" button to see it work.


http://ge.tt./7Vnubcv2

Cheerio,
vcoolio.