extracting data

[vac]

I'm refrasing my origional question here, but it may help.
I want to extact the data form a worksheet lets say cells B3, C4,D5, and place this data in another worksheet in the next available row.
I'm sure this can be done, can anyone help please

[Colo]

Hi vak,

Please copy this into a standard module, and try this.

Sub test()

    Dim sh1 As Worksheet, sh2 As Worksheet, i As Integer

    '///You will need to modify the sheet name

    Set sh1 = Sheets("Sheet1")    ' Name of Data Sheet

    Set sh2 = Sheets("Sheet2")    'Name of Another Sheet

    With sh1

        For i = 2 To 4

            sh2.Cells(Rows.Count, i).End(xlUp).Offset(1).Value = .Cells(i + 1, i).Value

        Next

    End With

    Set sh1 = Nothing: Set sh2 = Nothing

End Sub

[vac]

Many thanks Colo,
It works perfect.
How do I change the code to make it pick out the data from different cells, I think the cells I need the info from are D8, C1, B24, thanks again

[Colo]

Hi vac,

An easy one is...

Sub test2()

  '///You will need to modify the sheet name

    Sheets("Sheet2").[B65536].End(xlUp).Offset(1).Value = Sheets("Sheet1").[B24].Value

    Sheets("Sheet2").[C65536].End(xlUp).Offset(1).Value = Sheets("Sheet1").[C1].Value

    Sheets("Sheet2").[D65536].End(xlUp).Offset(1).Value = Sheets("Sheet1").[D8].Value

End Sub

[RET79]

You can do this with the indirect function perhaps? Take a look at this it may give you some ideas:

Microsoft Excel - RHOD1.xls______________Running: xl2000 : OS = Windows (32-bit) 4.10
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1		=	LIAB COLUMN BREAKDOWN
	A	B	C	D	E
1	LIAB COLUMN BREAKDOWN
2
3	File description	Index	File path
4	Form 51 results including new cost of bonus.	1	C:WINDOWSDesktopExcel stuff[TESTFILE.xls]
5	Cost of Terminal Bonus for Form 58	2	C:WINDOWSDesktopExcel stuff[TESTFILE.xls]
6		3
7		4
8
9	Name	File	Sheet	Range	Value
10	DATA	1	Sheet1	A1:A20	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B10-1,0)&C10&%22%27!%22&D10))")>59994
11		2	Sheet1	A4	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B11-1,0)&C11&%22%27!%22&D11))")>437
12		1	Sheet2	A5	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B12-1,0)&C12&%22%27!%22&D12))")>1135
13		1	Sheet1	B1:B10	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B13-1,0)&C13&%22%27!%22&D13))")>5646
14		2	Sheet2	A3	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B14-1,0)&C14&%22%27!%22&D14))")>645
15	OBASS_NPWPRP	1	Sheet1	B5	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B15-1,0)&C15&%22%27!%22&D15))")>23
16		2	Sheet1	B2	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B16-1,0)&C16&%22%27!%22&D16))")>253
17		2	Sheet1	B1:B10	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B17-1,0)&C17&%22%27!%22&D17))")>5646
18		2	Sheet1	B3	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B18-1,0)&C18&%22%27!%22&D18))")>2
19		2	Sheet1	B6	:alert("=SUM(INDIRECT(%22%27%22&OFFSET($C$4,B19-1,0)&C19&%22%27!%22&D19))")>34
Liab

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by[HtmlMaker V1.25]
If you want this code, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Note: To read in the values the file links must be open.

RET79

[vac]

Many thanks ret79, colo's example works perfect
Vac

[vac]

Colo your a genius! it works perfect
I'm in your debt
Vac

[Colo]

Hi vac, You are welcom

Hi RET79,
Nice answer!

About HtmlMaker
Java Script is used to show formulas, a message box is not started
when the characters ['] or ["] are inputted into one cell.

But we can see the formula on the status bar when mouse is on hyperlinks.

And new version is
click here to download

[Colo]

Hi RET79,
The latest version was updated.(Not beta version)

When the characters ['] or ["] are inputted into one cell, it is no problem.

Microsoft Excel - Book1 ___Running: xl97 : OS = Windows (32-bit) 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
E10E11E12E13E14E15E16E17E18E19 =
	A	B	C	D	E
1	LIAB COLUMN BREAKDOWN
2
3	File description	Index	File path
4	Form 51 results including new cost of bonus.	1	C:WINDOWSDesktopExcel stuff[TESTFILE.xls]
5	Cost of Terminal Bonus for Form 58	2	C:WINDOWSDesktopExcel stuff[TESTFILE.xls]
6		3
7		4
8
9	Name	File	Sheet	Range	Value
10	DATA	1	Sheet1	A1:A20	59994
11		2	Sheet1	A4	437
12		1	Sheet2	A5	1135
13		1	Sheet1	B1:B10	5646
14		2	Sheet2	A3	645
15	OBASS_NPWPRP	1	Sheet1	B5	23
16		2	Sheet1	B2	253
17		2	Sheet1	B1:B10	5646
18		2	Sheet1	B3	2
19		2	Sheet1	B6	34
Sheet1

To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.0] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo