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

 

[bsquad]

I am not exactly for sure what you mean based on your description; but I am pretty sure I get from the context

Sub SpellCheck()
    Dim TargetSheet As Worksheet
        Set TargetSheet = Worksheets("Sheet5")
    Dim OpenSourceWorkbook As Variant
    Dim SourceWorkbook As Workbook
        Set SourceWorkbook = ThisWorkbook
    Dim i As Integer
        Application.ScreenUpdating = False
        For i = 1 To 4
            Dim ER As Range
                Set ER = Worksheets(i).UsedRange
            With Worksheets(i)
                ER.Copy
            End With
                If ThisWorkbook.Worksheets.TargetSheet.Range("A1").Value = "" Then
                    ThisWorkbook.Worksheets.TargetSheet.Range("A1").PasteSpecial Paste:=xlPasteAll
                Else
                On Error Resume Next
                    ThisWorkbook.Worksheets.TargetSheet.Range("A1").End(xlDown).Offset(1).PasteSpecial Paste:=xlPasteAll
                End If
                Application.CutCopyMode = False
        Next i


End Sub

 

[hendrikbez]

Thank you for the code, but I getting compile error "Method or data member not found. on line

if ThisWorkbook.Worksheets.TargetSheet.Range("A1").Value = "" Then

I have change this line to the name of my sheet that I want the info on.

Set TargetSheet = Worksheets("Allinfo")

Ok let me try to explain again. (English not my first language)

I have 5 sheets, on the last sheet number 5, I want all the info form sheet 1 to sheet 4 to be copied (only certain columns)
but the problem is, that on all 4 sheet the rows will be different each day. sometimes more and sometimes less info.
say on sheet 1 there will be 5 rows for today, then tomorrow it can be 10 rows.

 

[bsquad]

no worries on the description,

the rows shouldn't be an issue; but when you mention - only certain columns;
do you mean its fixed >>where its a specific column region each time
or
is this also variable as well?

Right now the code is to take all of the columns that have data

apologizes for the 'Method or data member not found' error -
the below should work but let me know if you are still having issues

Sub SpellCheck()
    Dim TargetSheet As Worksheet
        Set TargetSheet = ThisWorkbook.Sheets("Allinfo")
    Dim OpenSourceWorkbook As Variant
    Dim SourceWorkbook As Workbook
        Set SourceWorkbook = ThisWorkbook
    Dim I As Integer
        Application.ScreenUpdating = False
        For I = 1 To 4
            Dim ER As Range
                Set ER = Worksheets(I).UsedRange
            With Worksheets(I)
                ER.Copy
            End With
                If TargetSheet.Range("A1").Value = "" Then
                    TargetSheet.Range("A1").PasteSpecial Paste:=xlPasteAll
                Else
                On Error Resume Next
                    TargetSheet.Range("A1").End(xlDown).Offset(1).PasteSpecial Paste:=xlPasteAll
                End If
                Application.CutCopyMode = False
        Next I


End Sub

 

[hendrikbez]

bsquad the code is running,but not as it should. Hope you can help me with this......

On all the 4 sheets where the info is on, that will go to allinfo sheet start at row 9 using this columns A,B,C and E
All 4 sheet info from row 9
on Sheet allinfo, I have the following columns A,B,C,D,E and F starting row 4

info from sheet 1-4 Column A must go to column B on allinfo, from this info i change something and put info in Column A and C
Info from sheet 1-4 Column B must go to column D on allinfo
Info from sheet 1-4 Column C must go to column E on allinfo
Info from sheet 1-4 Column E must go to column F on allinfo

there are also columns after F in allinfo, but that info is coming from info in my columns.

 

[bsquad]

Ok maybe visually it will help.
When I have 'Source' in the below, I mean Source = Sheets 1 - 4

this is as if we were looking at the 'Allinfo' tab

let me know if this is correct and I can figure something out

Excel 2013 32 bit

	A​	B​	C​	D​	E​	F​
1​	Allinfo_ColumnA_Row1	Allinfo_ColumnB_Row1	Allinfo_ColumnC_Row1	Allinfo_ColumnD_Row1	Allinfo_ColumnE_Row1	Allinfo_ColumnF_Row1
2​
3​
4​		Source_ColumnA_Row_9		Source_ColumnB_Row_9	Source_ColumnC_Row_9	Source_ColumnE_Row_9
5​		Source_ColumnA_Row_10		Source_ColumnB_Row_10	Source_ColumnC_Row_10	Source_ColumnE_Row_10
6​		Source_ColumnA_Row_11		Source_ColumnB_Row_11	Source_ColumnC_Row_11	Source_ColumnE_Row_11
7​		Source_ColumnA_Row_12		Source_ColumnB_Row_12	Source_ColumnC_Row_12	Source_ColumnE_Row_12
8​		Source_ColumnA_Row_13		Source_ColumnB_Row_13	Source_ColumnC_Row_13	Source_ColumnE_Row_13
9​		Source_ColumnA_Row_14		Source_ColumnB_Row_14	Source_ColumnC_Row_14	Source_ColumnE_Row_14
10​		Source_ColumnA_Row_15		Source_ColumnB_Row_15	Source_ColumnC_Row_15	Source_ColumnE_Row_15
11​		Source_ColumnA_Row_16		Source_ColumnB_Row_16	Source_ColumnC_Row_16	Source_ColumnE_Row_16
12​		Source_ColumnA_Row_17		Source_ColumnB_Row_17	Source_ColumnC_Row_17	Source_ColumnE_Row_17
13​		Source_ColumnA_Row_18		Source_ColumnB_Row_18	Source_ColumnC_Row_18	Source_ColumnE_Row_18
14​		Source_ColumnA_Row_19		Source_ColumnB_Row_19	Source_ColumnC_Row_19	Source_ColumnE_Row_19
15​		Source_ColumnA_Row_20		Source_ColumnB_Row_20	Source_ColumnC_Row_20	Source_ColumnE_Row_20
16​		Source_ColumnA_Row_21		Source_ColumnB_Row_21	Source_ColumnC_Row_21	Source_ColumnE_Row_21
17​		Source_ColumnA_Row_22		Source_ColumnB_Row_22	Source_ColumnC_Row_22	Source_ColumnE_Row_22
18​		Source_ColumnA_Row_23		Source_ColumnB_Row_23	Source_ColumnC_Row_23	Source_ColumnE_Row_23
19​		Source_ColumnA_Row_24		Source_ColumnB_Row_24	Source_ColumnC_Row_24	Source_ColumnE_Row_24
20​		Source_ColumnA_Row_25		Source_ColumnB_Row_25	Source_ColumnC_Row_25	Source_ColumnE_Row_25
21​		Source_ColumnA_Row_26		Source_ColumnB_Row_26	Source_ColumnC_Row_26	Source_ColumnE_Row_26
22​		Source_ColumnA_Row_27		Source_ColumnB_Row_27	Source_ColumnC_Row_27	Source_ColumnE_Row_27
23​		Source_ColumnA_Row_28		Source_ColumnB_Row_28	Source_ColumnC_Row_28	Source_ColumnE_Row_28
24​		Source_ColumnA_Row_29		Source_ColumnB_Row_29	Source_ColumnC_Row_29	Source_ColumnE_Row_29
25​		Source_ColumnA_Row_30		Source_ColumnB_Row_30	Source_ColumnC_Row_30	Source_ColumnE_Row_30

Sheet: AllInfo

 

[bsquad]

If the above is what you are looking for; code is below for it. (I just went ahead anyway; it didn't take to long - but let me know if its still not what you are looking for)
the code below is a assuming you have Sheet1 as the first tab Sheet2 as the second tab...etc if not then just adjust the 'For x =' positions

Sub RC_Index()
    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
        
        For x = 1 To 4
                For SRIndex = 9 To ThisWorkbook.Worksheets(x).UsedRange.Rows.Count
                    If ThisWorkbook.Worksheets(x).Cells(SRIndex, 1) <> "" Or 1 = 1 Then
                        Dim SValue As String
                            SValue = ThisWorkbook.Worksheets(x).Cells(SRIndex, SCIndex).Value
                            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
                    End If
                Next
        Next x


End Sub

 

[hendrikbez]

Yes that is want I need it to be

 

[hendrikbez]

Thank you, will only be able to test it tomorrow, will let you know how it is going

 

[hendrikbez]

bsquad

I have use this code, it only put the first sheet info in (like it should look), then it look like it is thinking, but none other data from the other 3 sheets is copied to Allinfo.

I don't think is is because my sheets have other names, but it is working it the right order.