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]

It is working, I have go down in the allinfo page and the info is there, but there is a lot of blank spaces between them.

Could be because, when I import a text file, it has many rows, I delete most of them because they are not needed.
Then I run your code, and it runs, but with a lot of empty rows between them (the Sheets info)

 

[bsquad]

yes what I gave you does not account for blank spaces, and is absolutely the reason you are getting blank spaces; you didn't mention that to begin with. I do have a similar code that would counter act it, however given the mapping you have between your columns gets extremely complicated - since the columns to not map in sequential order (or at least extremely complicated to where its above my pay grade knowledge; with Excel there's thousands of ways of writing code, just like SQL so I am not saying my way is the only way).
an example, you are mapping from columns 1,2,3, (A, B, C respectively) then jump to 5. Its almost as if you would need to use STEP just for that column in an IF statement

My suggestion would be to remapped you columns before you run this macro if you want to eliminate the blank spaces
OR
on your Import code, eliminate the spaces

 

[hendrikbez]

There is no spaces between the rows in each sheet,
I import the text file, then I delete all that I don't need, I always keep the top rows in each sheet.

 

[bsquad]

so there is no blank spaces in 'Allinfo' after running the macro?

 

[hendrikbez]

On each sheet, after deleting rows that I do not need, then I run your code. It then shows the first sheet, and then it shows a lot of empty spaces or rows, then the rows of the next sheet and so on

 

[bsquad]

if its a consistent amount of rows between sheet1 and sheet2, there might be something with how you are deleting the rows. The code below (which should be the same as what I had posted before) is generating the results. I'd have to see the sheet otherwise.

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

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​		Sheet1_ColumnA_Row_9		Sheet1_ColumnB_Row_9	Sheet1_ColumnC_Row_9	Sheet1_ColumnE_Row_9
5​		Sheet1_ColumnA_Row_10		Sheet1_ColumnB_Row_10	Sheet1_ColumnC_Row_10	Sheet1_ColumnE_Row_10
6​		Sheet1_ColumnA_Row_11		Sheet1_ColumnB_Row_11	Sheet1_ColumnC_Row_11	Sheet1_ColumnE_Row_11
7​		Sheet1_ColumnA_Row_12		Sheet1_ColumnB_Row_12	Sheet1_ColumnC_Row_12	Sheet1_ColumnE_Row_12
8​		Sheet1_ColumnA_Row_13		Sheet1_ColumnB_Row_13	Sheet1_ColumnC_Row_13	Sheet1_ColumnE_Row_13
9​		Sheet1_ColumnA_Row_14		Sheet1_ColumnB_Row_14	Sheet1_ColumnC_Row_14	Sheet1_ColumnE_Row_14
10​		Sheet1_ColumnA_Row_15		Sheet1_ColumnB_Row_15	Sheet1_ColumnC_Row_15	Sheet1_ColumnE_Row_15
11​		Sheet1_ColumnA_Row_16		Sheet1_ColumnB_Row_16	Sheet1_ColumnC_Row_16	Sheet1_ColumnE_Row_16
12​		Sheet1_ColumnA_Row_17		Sheet1_ColumnB_Row_17	Sheet1_ColumnC_Row_17	Sheet1_ColumnE_Row_17
13​		Sheet1_ColumnA_Row_18		Sheet1_ColumnB_Row_18	Sheet1_ColumnC_Row_18	Sheet1_ColumnE_Row_18
14​		Sheet1_ColumnA_Row_19		Sheet1_ColumnB_Row_19	Sheet1_ColumnC_Row_19	Sheet1_ColumnE_Row_19
15​		Sheet1_ColumnA_Row_20		Sheet1_ColumnB_Row_20	Sheet1_ColumnC_Row_20	Sheet1_ColumnE_Row_20
16​		Sheet1_ColumnA_Row_21		Sheet1_ColumnB_Row_21	Sheet1_ColumnC_Row_21	Sheet1_ColumnE_Row_21
17​		Sheet1_ColumnA_Row_22		Sheet1_ColumnB_Row_22	Sheet1_ColumnC_Row_22	Sheet1_ColumnE_Row_22
18​		Sheet1_ColumnA_Row_23		Sheet1_ColumnB_Row_23	Sheet1_ColumnC_Row_23	Sheet1_ColumnE_Row_23
19​		Sheet1_ColumnA_Row_24		Sheet1_ColumnB_Row_24	Sheet1_ColumnC_Row_24	Sheet1_ColumnE_Row_24
20​		Sheet1_ColumnA_Row_25		Sheet1_ColumnB_Row_25	Sheet1_ColumnC_Row_25	Sheet1_ColumnE_Row_25
21​		Sheet1_ColumnA_Row_26		Sheet1_ColumnB_Row_26	Sheet1_ColumnC_Row_26	Sheet1_ColumnE_Row_26
22​		Sheet1_ColumnA_Row_27		Sheet1_ColumnB_Row_27	Sheet1_ColumnC_Row_27	Sheet1_ColumnE_Row_27
23​		Sheet1_ColumnA_Row_28		Sheet1_ColumnB_Row_28	Sheet1_ColumnC_Row_28	Sheet1_ColumnE_Row_28
24​		Sheet1_ColumnA_Row_29		Sheet1_ColumnB_Row_29	Sheet1_ColumnC_Row_29	Sheet1_ColumnE_Row_29
25​		Sheet1_ColumnA_Row_30		Sheet1_ColumnB_Row_30	Sheet1_ColumnC_Row_30	Sheet1_ColumnE_Row_30
26​		Sheet2_ColumnA_Row_9		Sheet2_ColumnB_Row_9	Sheet2_ColumnC_Row_9	Sheet2_ColumnE_Row_9
27​		Sheet2_ColumnA_Row_10		Sheet2_ColumnB_Row_10	Sheet2_ColumnC_Row_10	Sheet2_ColumnE_Row_10
28​		Sheet2_ColumnA_Row_11		Sheet2_ColumnB_Row_11	Sheet2_ColumnC_Row_11	Sheet2_ColumnE_Row_11
29​		Sheet2_ColumnA_Row_12		Sheet2_ColumnB_Row_12	Sheet2_ColumnC_Row_12	Sheet2_ColumnE_Row_12
30​		Sheet2_ColumnA_Row_13		Sheet2_ColumnB_Row_13	Sheet2_ColumnC_Row_13	Sheet2_ColumnE_Row_13
31​		Sheet2_ColumnA_Row_14		Sheet2_ColumnB_Row_14	Sheet2_ColumnC_Row_14	Sheet2_ColumnE_Row_14
32​		Sheet2_ColumnA_Row_15		Sheet2_ColumnB_Row_15	Sheet2_ColumnC_Row_15	Sheet2_ColumnE_Row_15
33​		Sheet2_ColumnA_Row_16		Sheet2_ColumnB_Row_16	Sheet2_ColumnC_Row_16	Sheet2_ColumnE_Row_16
34​		Sheet2_ColumnA_Row_17		Sheet2_ColumnB_Row_17	Sheet2_ColumnC_Row_17	Sheet2_ColumnE_Row_17
35​		Sheet2_ColumnA_Row_18		Sheet2_ColumnB_Row_18	Sheet2_ColumnC_Row_18	Sheet2_ColumnE_Row_18
36​		Sheet2_ColumnA_Row_19		Sheet2_ColumnB_Row_19	Sheet2_ColumnC_Row_19	Sheet2_ColumnE_Row_19
37​		Sheet2_ColumnA_Row_20		Sheet2_ColumnB_Row_20	Sheet2_ColumnC_Row_20	Sheet2_ColumnE_Row_20
38​		Sheet2_ColumnA_Row_21		Sheet2_ColumnB_Row_21	Sheet2_ColumnC_Row_21	Sheet2_ColumnE_Row_21
39​		Sheet2_ColumnA_Row_22		Sheet2_ColumnB_Row_22	Sheet2_ColumnC_Row_22	Sheet2_ColumnE_Row_22
40​		Sheet2_ColumnA_Row_23		Sheet2_ColumnB_Row_23	Sheet2_ColumnC_Row_23	Sheet2_ColumnE_Row_23
41​		Sheet2_ColumnA_Row_24		Sheet2_ColumnB_Row_24	Sheet2_ColumnC_Row_24	Sheet2_ColumnE_Row_24
42​		Sheet2_ColumnA_Row_25		Sheet2_ColumnB_Row_25	Sheet2_ColumnC_Row_25	Sheet2_ColumnE_Row_25
43​		Sheet2_ColumnA_Row_26		Sheet2_ColumnB_Row_26	Sheet2_ColumnC_Row_26	Sheet2_ColumnE_Row_26
44​		Sheet2_ColumnA_Row_27		Sheet2_ColumnB_Row_27	Sheet2_ColumnC_Row_27	Sheet2_ColumnE_Row_27
45​		Sheet2_ColumnA_Row_28		Sheet2_ColumnB_Row_28	Sheet2_ColumnC_Row_28	Sheet2_ColumnE_Row_28
46​		Sheet2_ColumnA_Row_29		Sheet2_ColumnB_Row_29	Sheet2_ColumnC_Row_29	Sheet2_ColumnE_Row_29
47​		Sheet2_ColumnA_Row_30		Sheet2_ColumnB_Row_30	Sheet2_ColumnC_Row_30	Sheet2_ColumnE_Row_30
48​		Sheet3_ColumnA_Row_9		Sheet3_ColumnB_Row_9	Sheet3_ColumnC_Row_9	Sheet3_ColumnE_Row_9
49​		Sheet3_ColumnA_Row_10		Sheet3_ColumnB_Row_10	Sheet3_ColumnC_Row_10	Sheet3_ColumnE_Row_10
50​		Sheet3_ColumnA_Row_11		Sheet3_ColumnB_Row_11	Sheet3_ColumnC_Row_11	Sheet3_ColumnE_Row_11
51​		Sheet3_ColumnA_Row_12		Sheet3_ColumnB_Row_12	Sheet3_ColumnC_Row_12	Sheet3_ColumnE_Row_12
52​		Sheet3_ColumnA_Row_13		Sheet3_ColumnB_Row_13	Sheet3_ColumnC_Row_13	Sheet3_ColumnE_Row_13
53​		Sheet3_ColumnA_Row_14		Sheet3_ColumnB_Row_14	Sheet3_ColumnC_Row_14	Sheet3_ColumnE_Row_14
54​		Sheet3_ColumnA_Row_15		Sheet3_ColumnB_Row_15	Sheet3_ColumnC_Row_15	Sheet3_ColumnE_Row_15
55​		Sheet3_ColumnA_Row_16		Sheet3_ColumnB_Row_16	Sheet3_ColumnC_Row_16	Sheet3_ColumnE_Row_16

Sheet: AllInfo

 

[hendrikbez]

When running your code, it makes about 400 empty rows between sheet one and sheet 2 data, It is a pity that I cannot add a test file for you to see.

 

[hendrikbez]

Here is the file, hope you can get it

test.xlsm - Google Drive

on the all sheet, I press Get All button on top left.

 

[hendrikbez]

bsquad, we are nearly winning. just have to also find a way to delete rows (hundreds of them)

I have delete by selecting the rows now the rows that I do not need, and it nearly working like it should, it is now showing the next sheet after the first one and so on, but now it take the last row of each sheet and replace it with the first row of the next sheet.

eg

it should look like this

AAA from sheet 1, then BBA form sheet2, then CCA from sheet4 and DDA from sheet 4, on sheet 5 it should look like this ( this just example with one column)

AAA
AAB
AAC
AAD
BBA
BBB
BBC
BBD
BBE
CCA
CCB
CCC
DDA
DDB


But is shows it like this, each last row of each sheet is not showing

AAA
AAB
AAC
BBA
BBB
BBC
BBD
CCA
CCB
DDA

 

[bsquad]

I am thinking its how you are importing and deleting - to my knowledge. When I run it after deleting rows it seems to work fine, however I know 'UsedRange' can somewhat be finicky at times.

Try replacing

For SRIndex = 9 To ThisWorkbook.Worksheets(x).UsedRange.Rows.Count

with this

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

to see if it makes any difference