copy and paste specific multiple ranges from the same sheet to the last column

Question

Hello-

Please see attached dummy file.  I just need to copy and paste specific ranges from a daily excel dashboard into another sheet in the same file.  Here is my code below with the attached file.   The problem that I'm having is instead of last row, I want it to be basically choose the last column since daily a new column will be added and chose those specific ranges only to copy and paste from the table to sheet1 as the data is added with the column and not row.  I have highlighted in green the ranges that I need top copy and paste daily.

Dummy File notes:

Source sheet- is the source data that comes in 

Sheet1-is where I already have the headers and column a( so no need to copy the header or column A. 

Dummy File Below:

https://1drv.ms/x/s!AmxTHYioikr1iigC7iDMi1JbRxjc

Code Below:

Private Sub CommandButton1_Click()

Sheets("Source").Select

 Range("B10:W14").Select

Selection.Copy

 Sheets("Sheet1").Select

Range("B2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

 :=False, Transpose:=False

Sheets("Source").Select

    Range("B19:W22").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Sheet1").Select

    Range("B7").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

Answer 1

Hi Gloria,

I was looking for someone to help me with my VBA code that I provided above for me to select multiple specific ranges with last column and row for each multiple range.  Can you please help with that since I don't want to use a formula?

@ZH_M

Hi

Find in the link a file with the VBA solution as per your requirements

Notes:

1- Sheets("Destination") refers to  Sheets("Sheet1") on your original code

2- On the file, there are 2 VBA solutions options

Option 1

 Using a regular macro module with a Command Button.

*******************************************************************************************************

Private Sub CommandButton1_Click()

Dim DateRange As Range, lastColumn As Long

Dim firstRange As Range, secondRange As Range

With Sheets("Source")

        lastColumn = .UsedRange.Columns.Count

        Set DateRange = .Range("B1").Resize(1, lastColumn - 1)

        Set firstRange = .Range("A10").Resize(5, lastColumn)

        Set secondRange = .Range("A19").Resize(4, lastColumn)

End With

Application.ScreenUpdating = False

        Sheets("Destination").Cells.Clear  ''' To Delete "Old data"

        Sheets("Destination").Range("A1").Value = "Row Label"

        DateRange.Copy Sheets("Destination").Range("B1")   ''' To Paste Dates

        firstRange.Copy Sheets("Destination").Range("A2")     ''' To paste first group values

        secondRange.Copy Sheets("Destination").Range("A7")    ''' To paste second group values

        Sheets("Destination").Columns.AutoFit   ''' To tide up output data.

  Application.ScreenUpdating = True

MsgBox "Job Done"

End Sub

************************************************************************************************

Option 2

Using the change event of the Source sheet.

In this case, the macro will run automatically once you deactivate (leave) the "Source" sheet to select any other sheet in the workbook. So when you finish the update on "Source" sheet, once you select the "Destination" sheet it will update instantly

*************************************************************************************************

Private Sub Worksheet_Deactivate()

Dim DateRange As Range, lastColumn As Long

Dim firstRange As Range, secondRange As Range

With Me

        lastColumn = .UsedRange.Columns.Count

        Set DateRange = .Range("B1").Resize(1, lastColumn - 1)

        Set firstRange = .Range("A10").Resize(5, lastColumn)

        Set secondRange = .Range("A19").Resize(4, lastColumn)

End With

  Application.ScreenUpdating = False

        Sheets("Destination").Cells.Clear  ''' To Delete "Old data"

        Sheets("Destination").Range("A1").Value = "Row Label"

        DateRange.Copy Sheets("Destination").Range("B1")   ''' To Paste Dates

        firstRange.Copy Sheets("Destination").Range("A2")     ''' To paste first group values

        secondRange.Copy Sheets("Destination").Range("A7")    ''' To paste second group values

        Sheets("Destination").Columns.AutoFit   ''' To tide up output data.

  Application.ScreenUpdating = True

MsgBox "Destination sheet, Updated successfully"

End Sub

***************************************************************************************************

Here is the link to the workbook.

https://1drv.ms/x/s!AjGRD1TlwpAGmGMqYVxOEpVdNGRS?e=goC84C

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 2

I'm glad it worked for you

Thanks for your positive feedback

Regarding your last question

Add the following line to the code just before 

Sheets("Destination").Rows("1:1").NumberFormat = "ddd d/m/yyyy"  '' Format Date headers

Just before line Application.ScreenUpdating = True

I updated the code in the file on the link

Regards

Jeovany

Answer 3

Hi. Thank you for reaching us, i'm an independent Microsoft Excel advisor and fellow user here to provide you assistance.

First, please insert row in in each sheet on the first row, above the date and put down numbers 1,2,3... and so on on each column of the first row

Answer 4

then use vlookup formula, on the sheet

In the Formula Bar, type =VLOOKUP

1. enter your lookup value or click the label name and a dollar sign before the letter
2. Enter your table array or lookup table, the range of data you want to search ( click on the source sheet and click on the first label name until the last column and row you want to copy and put on dollar sign before each number and letter.)
3. Enter column index number. ( click on the inserted row on the top portion, the first row column B, then put dollar sign before the number)
4. Enter the range lookup value, FALSE.

your formula should look like this

vlookup($A3,source!$B$4:$W$30,source!B$1,FALSE)

Answer 5

then drag on the formula till the last column and copy the formula in each row