copying groups of date between documents

[msnook]

A little help would be greatly appreciated and I thank everyone in advance. I am useing the coding below. I need to copy data from one file to another. When I exprt my data to this file sometimes it doesn't export all of the column headers from the access file. I don't ahve access to change that report so I need to deal with what I am dealt. The only way I have found is that the first entry in what I want to copy is a "1". Then I just need to count the number of times "1" comes up and copy those particular groups of data. (ie if b = 1 then could also be if b = 12 then) If anyone has a better idea I would love the input. Also if I could set the '.Range ("E10")' up as a variable I could cut down on the coding involved.


If b = 1 Then
With Worksheets("12")

If ((Worksheets("12").Range("C" & a)) = "1") Then

.Range("D" & a, "D" & a + 3).Copy
Workbooks("compileStats.xls").Worksheets("Sheet1").Range("E10").PasteSpecial xlPasteAll
.Range("F" & a, "F" & a + 3).Copy
Workbooks("compileStats.xls").Worksheets("Sheet1").Range("G10").PasteSpecial xlPasteAll
b = b + 1
End If

End With

End If

 

[SkipVought]

Hi,

Please explain to us, what apparently is very clear to you.

What is a and what is b? Where do thy come from?

Why are you testing for the CHARACTER 1 rather than the NUMBER 1?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[AnotherHiggins]

-> When I export my data to this file sometimes it doesn't export all of the column headers from the access file.

If all you are after is populating column headers, why not just do it straight out every time, rather than getting it from another sheet? The column headers are always the same, right?

[A1] = "Header1"
[B1] = "Header2"
...

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[msnook]

Sorry for limited information. There is an ACCESS document that is produced showing a large amount of data. I am exporting all of the data into excel so that I can extract just the data I want. The data appears in the followin format and the amount of data could change so that the positioning of the 1's would change.
1 636 635
2 173 43
3 39 35

1 25 22
2 13 4
3 3 3

1 66 63
2 17 3
3 39 35

I am trying to set up a macro where I can designate which gouping of data I need on my document. If I enter:
If b = 1 (or 3) then the data I return is
636 635
173 43
39 35
or
66 63
17 3
39 35

a and b are used as variables to mark my place
a is counting from 1 to 1500 looking for the 1's,
and b is counting up the number of 1's found so that it can decide which gouping of numbers to pull.
I am trying to accomplish this in the fewest amount of steps possible. I could set up seperate commands for each grouping but would rather search for which 1's i am looking for. It would all be easier if the heading came through from access. Once again thanks for all of the help.

I can make it work as below:
Sub newsearch()

b = 1

Workbooks.Open ("C:\Documents and Settings\snook_mi\Desktop\stats.xls")

For a = 1 To 1500

If b = 1 Then
With Worksheets("12")

If ((Worksheets("12").Range("C" & a)) = "1") Then

.Range("D" & a, "D" & a + 3).Copy
Workbooks("compileStats.xls").Worksheets("Sheet1").Range("E10").PasteSpecial xlPasteAll
.Range("F" & a, "F" & a + 3).Copy
Workbooks("compileStats.xls").Worksheets("Sheet1").Range("G10").PasteSpecial xlPasteAll
b = b + 1
End If
End With
End If
If b = 2 Then
With Worksheets("12")

If ((Worksheets("12").Range("C" & a + 1)) = "1") Then

.Range("D" & a + 1, "D" & a + 4).Copy
Workbooks("compileStats.xls").Worksheets("Sheet1").Range("K10").PasteSpecial xlPasteAll
.Range("F" & a + 1, "F" & a + 4).Copy
Workbooks("compileStats.xls").Worksheets("Sheet1").Range("M10").PasteSpecial xlPasteAll
b = b + 1
End If
End With
End If
Next a

Workbooks("stats.xls").Close SaveChanges:=True

End Sub

 

[SkipVought]

Is this a REPORT from Access? YUK! It sure does not look like data from a Table.

I avoid, at almost all cost, working with reports if data is available. You know that you can QUERY Access tables and queries directly from Excel and return a subset of data, based on your criteria.

Now to your examples

[tt]
"I am trying to set up a macro where I can designate which gouping of data I need on my document. If I enter:
If b = 1 (or 3) then the data I return is..."

636 635
173 43
39 35
or
66 63
17 3
39 35
[/tt]
This appears to be the FIRST and THIRD GROUPS, rather than column 1 values of 1 or 3. Is that correct?

So if b=3 then it would be the THIRD group you are looking for...
[tt]
66 63
17 3
39 35
[/tt]
and not all the row that have a 3 in the first column????


Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[msnook]

Once again sorry for the confusing language it makes sense when i am typing it. I agree with what you say but in this particular instance the only way I can get my data is through this report. It is frustrating because sometimes when i export it includes the headings (which is a whole lot easier to work with) and sometimes it doesn't. The data looks just like the below.

A B C

1 636 635
2 173 43
3 39 35
4 4 2
5 1 0
6 1 1
7 1 0
Text37: 855

there are many grouping like this in ACCESS document, I need to count everytime the #1 appears and then pull the four lines (1,2,3,4) from the groupings I want and put them into my excel spreadsheet. I will not be pulling from all of the groupins and the number of lines changes so that where in the EXCEL document they show up could potentially be different every time.

The b = statement I was using was a way to counting which grouping I was working with so that I could pull from the correct ones.

Once again thanks for the assistance.

 

[SkipVought]

I'd use find

dim r as range
set r = YourSheetObject.Columns(1).find(1)
if not r is nothing then
  if r.value = 1 then
     r.currentregion.copy
'then past ithe group where you want.
  end if
end if

for the next find use...

set r = YourSheetObject.Columns(1).find(1, after:=r)

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}