0

I have a few columns (B to G), starting numbers in B3. Today I select data and copy it with macro into cell B3. Tomorrow I want the data copied to cell B4, the day after to B5.

When the month is over data should be copied to C3, ....

Now I have to change the desired B3, B4 everyday in the macro. How to do it automaticaly.

thanks in advance

Part of code:

Range("B47").Formula = "=SUM(B46-C46)"
Range("B47").Activate
Selection.Copy
Sheets("VIP Punten").Select
Range("C33").Select "THIS HAS TO CHANGE EVERYDAY"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Nopelken
  • 11
  • 1
  • [Find the last cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba)? – BigBen Sep 01 '20 at 18:55
  • 1
    obligatory: [don't use select and activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) (generally) – Warcupine Sep 01 '20 at 19:05
  • 1
    Rather than trying to select a cell dynamically, learn how to do what you want to do while selecting no cells at all. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248) (same link as @Warcupine gave, but hearing it twice can't hurt). – John Coleman Sep 01 '20 at 19:05

2 Answers2

0

You have to find the last row and the last column of your table.

Dim last_row As Long
Dim last_column As Long

last_column = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
last_row = ActiveSheet.Cells(Rows.Count, last_column).End(xlUp).Row

This you can apply to your code like:

Dim last_row As Long
Dim last_column As Long


Range("B47").Formula = "=SUM(B46-C46)"
Range("B47").Activate
Selection.Copy
Sheets("VIP Punten").Select

last_column = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
last_row = ActiveSheet.Cells(Rows.Count, last_column).End(xlUp).Row

Cells(last_row , last_column ).Select "THIS HAS TO CHANGE EVERYDAY"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Other advise: For optimise your code try to avoid use .Select is more optimal cut it like this

Before:

Cells(last_row , last_column ).Select 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

After:

Cells(last_row , last_column ).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

I hope I have helped you :)

asantz96
  • 611
  • 5
  • 15
  • When I do so, text comes in to G1, but should come in B3 – Nopelken Sep 01 '20 at 19:23
  • Okay, thats because you have headers and I didn't know it. I have modified my code, try now – asantz96 Sep 01 '20 at 19:27
  • text is going to right column, but goes to last line instead of line B3 – Nopelken Sep 01 '20 at 19:37
  • My failure, I thought that you only have one row for the header. However, you have two rows (B1, B2). Try know, I have only change the row that starts to count for the last column. – asantz96 Sep 01 '20 at 19:41
  • It is still not correct. So, I have Column A for the date, B for the Data. So, today the data from Cell B47 in Sheet A has to be copied to Sheet B, Cell B3. Tomorrow, data from Cell B47 in Sheet A has to be copied to Sheet B, Cell B4, .... – Nopelken Sep 01 '20 at 20:19
0

So, I tried

Dim ValueCell as Variant

Dim wb1 as workbook

ValueCell = wb1.sheets("Blad1").range("B2").value

wb1.sheets("Blad2").range("D1") = ValueCell"

But get error on ValueCell = ....

Nopelken
  • 11
  • 1