Change in VBA code

[KlausW]

Hi, I have a little challenge. I use this VBA code to consolidate my sheets. It works really well.
I would like the data to be inserted in K9, but do not know how to do.
All help will be appreciated.
Best Regards
Klaus W

Sub Opsamling()
    Dim wksHent As Range
    Dim wksSaml As Worksheet
    Dim sidsterk As Long
    Dim kopiomr  As Range
    Dim kolonner As Variant
    kolonner = Split(Names("HentKolonner").RefersToRange.Value2, ":")
    Set wksSaml = Worksheets(Names("SamlearkNavn").RefersToRange.Value2)
    wksSaml.Cells.ClearContents
    For Each wksHent In Names("OpsamlingFra").RefersToRange.Cells
        sidsterk = Worksheets(wksHent.Value2).Range("A" & Worksheets(wksHent.Value2).Rows.Count).End(xlUp).Row
        'kopi dataomkr
        Set kopiomr = Worksheets(wksHent.Value2).Range(kolonner(0) & "2:" & kolonner(1) & sidsterk)
        wksSaml.Range("k" & wksSaml.Rows.Count).End(xlUp).Offset(1, 0).Resize(kopiomr.Rows.Count, kopiomr.Columns.Count).Value2 = kopiomr.Value2
        'kopi overskrift
        'wksSaml.Range("A1").EntireRow.Value2 = Worksheets(wksHent.Value2).Range("A1").EntireRow.Value2
    Next
End Sub

 

[Siddharth Rout]

The line that you want to change is

wksSaml.Range("k" & wksSaml.Rows.Count).End(xlUp).Offset(1, 0)

This is where you are telling where to put the data. Currently it writes to the row after the last row. If you have a fixed address then simply use that

wksSaml.Range("K9").Resize(kopiomr.Rows.Count, kopiomr.Columns.Count).Value2 = kopiomr.Value2

 

[KlausW]

The line that you want to change is

wksSaml.Range("k" & wksSaml.Rows.Count).End(xlUp).Offset(1, 0)

This is where you are telling where to put the data. Currently it writes to the row after the last row. If you have a fixed address then simply use that

wksSaml.Range("K9").Resize(kopiomr.Rows.Count, kopiomr.Columns.Count).Value2 = kopiomr.Value2

Hi

The line that you want to change is

wksSaml.Range("k" & wksSaml.Rows.Count).End(xlUp).Offset(1, 0)

This is where you are telling where to put the data. Currently it writes to the row after the last row. If you have a fixed address then simply use that

wksSaml.Range("K9").Resize(kopiomr.Rows.Count, kopiomr.Columns.Count).Value2 = kopiomr.Value2

Hi

Siddharth Rout it looks like it is works. I will try it to day and return tomorrow. Hav a nice day. Best Regards Klaus, Denmark ​

 

[KlausW]

Hi

Hi

Siddharth Rout it looks like it is works. I will try it to day and return tomorrow. Hav a nice day. Best Regards Klaus, Denmark ​

Good morning Siddharth Rout, that's a little challenge. The VBA code only includes data if there is no formula. Where the VBA code is to retrieve the data, there are formulas in the cells. Best regards Klaus W

 

[Siddharth Rout]

Good morning Siddharth Rout, that's a little challenge. The VBA code only includes data if there is no formula. Where the VBA code is to retrieve the data, there are formulas in the cells. Best regards Klaus W

Not sure I understand you. Perhaps a screenshot of what you want to achieve may help for an accurate solution?