Get info from on sheet to other sheet, then use it and do other one the same

[hendrikbez]

I have 5 sheets. on my lat sheet I want to copy all info form sheet1 - 4 to sheet 5, did do a macro, but need help on how to do the following

on sheet 1 I can have info for about 2 to 12 rows
on sheet 2 I can have info for about 2 to 15 rows
on sheet 3 I can have info for about 2 to 120 rows
on sheet 4 I can have info for about 2 to 5 rows

I want to when I press the 2,3 or 4 macro it must start just after the last one of the sheet before it.
I am doing the first one, then drag down till I have lal 4 or 5 of first sheet in. Then I want to start the second one on the next row

Sub get1all()
'
' get1all Macro
'

'
    ActiveCell.FormulaR1C1 = "=UnixO!R[5]C[-1]"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=UnixO!R[5]C[-2]"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "=UnixO!R[5]C[-2]"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=UnixO!R[5]C[-1]"
    Range("F4").Select
End Sub

Sub get2all()
'
' get2all Macro
'

'
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=UnixN!R[1]C[-1]"
    Range("D8").Select
    ActiveCell.FormulaR1C1 = "=UnixN!R[1]C[-2]"
    Range("E8").Select
    ActiveCell.FormulaR1C1 = "=UnixN!R[1]C[-2]"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=UnixN!R[1]C[-1]"
    Range("F9").Select
End Sub

 

[hendrikbez]

bsquad
Thank you, the last code is working.

 

[hendrikbez]

Thank you, is is working 100% now, thanks for your help with this. If you have time can you please explain this code of yours to mr

 

[bsquad]

sure absolutely, can you tell me how much you already know so I know how much detail to put in? (i.e. do you understand the basic concept of 'Dim') - it's okay to say none, that's what this forum is here for.

 

[hendrikbez]

I do not know slot about this code

 

[bsquad]

I know this isn’t the best explanation,
>>what will help is if you have this code open in the VBA hitting F8 will take you step by step

    Dim x As Integer
    Dim SCIndex As Long
        SCIndex = 1
    Dim SRIndex As Long
        SRIndex = 9
    Dim TCIndex As Long
        TCIndex = 2
    Dim TRIndex As Long
        TRIndex = 4

in the first section with the 'Dim'
-those are your declarations, the naming of it is arbitrary and can be anything you want it to be >> 'x' or 'SCIndex' anything that will help you and others understand the reference. i.e. in this scenario where you see the 'Index' declarations SCIndex - SourceColumnIndex, SRIndex - SourceRowIndex, TCIndex - TargetColumnIndex, TRI - TargetRowIndex and so on.
the second part of it 'As Integer' is what you are specifying that variable to be - you can lookup what each one means or look it up in the Object Browser in the visual basic editor; I think its under the 'View' menu
-in a short sense Integer and Long are essentially the same thing, Integer just has a ceiling limit of like 32k or something and Long is anything above that.
Where I have the SCIndex = 1, that number/integer I want SCIndex to equal, - In this scenario, it means the column I want the code to start at on the source document

 For x = 1 To 4

'x' is the worksheet counter in this scenario
so in this particular code For X = 1 to 4
-the 'For' part is indicating you want to start a loop
--so its saying do whatever the code is below it four times

For SRIndex = 9 To ThisWorkbook.Worksheets(x).Cells(Rows.Count, SCIndex).End(xlUp).Row

Same 'For' concept here
it is specifying the range of data I want the code to look at
because we started with the 'For X =' above the code in this line where you see 'x', will be replaced with that number'
--so it will essentially be For SRIndex = 9 to ThisWorkbook.Worksheets(1).Cells(Rows.Count, SCIndex).End(xlUp).Row
----meaning from the dead bottom of the spreadsheet it goes up until there is data – so from row 9 to what ever that row from the bottom up is; that is the section or range the code is considering.

If ThisWorkbook.Worksheets(x).Cells(SRIndex, 1) <> "" Or 1 = 1 Then

-This is an IF statement to see if the cell has a value or not

With ThisWorkbook.Worksheets("Allinfo")
.Cells(TRIndex, 2).Value = Worksheets(x).Cells(SRIndex, 1).Value
.Cells(TRIndex, 4).Value = Worksheets(x).Cells(SRIndex, 2).Value
.Cells(TRIndex, 5).Value = Worksheets(x).Cells(SRIndex, 3).Value
.Cells(TRIndex, 6).Value = Worksheets(x).Cells(SRIndex, 5).Value
End With
TRIndex = TRIndex + 1

-so for this part where the cell locations equal each other; its going to put the value of the source worksheet>>(Worksheets(x)), in the same row location as the as the Allinfo
-In a short version, I am manually mapping the column location, the format for .Cells is >> .Cells(Row, Column)
-what is shifting the row or what is making it cycle is the TRIndex + 1 >> this adds the integer value each time the code cycles

 

[hendrikbez]

Thank you, for this and your help on this, it is working like it should