export table to multiple sheets

[TheShabz]

Hi everyone,

Currently I am charged with the task of writing a table containing a query results into Excel. The requirement is that each sheet can only contain 1000 records.



Now, in VBA I can create an excel book, sheet, name it, write to it, and save it. However, the closest I've come to writing to multiple sheets is using a counter that increments after every row and once it hits 1000, to create a new sheet. This is where I get stuck. I cant seem to get the rest to populate on the new sheet. This is something I should have in my stock VBA repetoir but I dont. Could anyone please provide?

Thank you

[ajetrumpet]

Now, in VBA I can create an excel book, sheet, name it, write to it, and save it. However, the closest I've come to writing to multiple sheets is using a counter that increments after every row and once it hits 1000, to create a new sheet. This is where I get stuck. I cant seem to get the rest to populate on the new sheet.

how do you write the first 1000 recs to the first sheet? you said you can get that far. if so, then the rest is easy. you say you get stuck after the increment hits 1000. WHAT are stuck on? WHAT can't you do? copy the data using code from one app to the other? get the focus to the new sheet? what does "THE REST" mean? the rest of what?

specificity is the key to getting help here.

[TheShabz]

wow thanks for pointing it out. Totally lost my train of thought mid sentence. The issue I'm having is creating the next sheet then having it continue populating the next sheet. Currently, it still continues to populate the first sheet. I assume I'm not pointing it to the second sheet properly.

Thanks.

[ajetrumpet]

wow thanks for pointing it out. Totally lost my train of thought mid sentence. The issue I'm having is creating the next sheet then having it continue populating the next sheet. Currently, it still continues to populate the first sheet. I assume I'm not pointing it to the second sheet properly.

Thanks.

there's alot to be learned in excel simply by recording yourself. go into excel, start recording a macro_add a new sheet by clicking the plus sign at the button_rename it by clicking and typing the new name if you want this step_then click on cell a1 in the new sheet. stop recording and read the code produced by the program. i'm sure you're good enough at that point to do the minimal translation it takes to float it on over to the access app and make it work!

[TheShabz]

I went ahead and created the macro and looked at the code. I found that .select was being used to accomplish this. however, using that I am still getting the same issue in my vba code.
This:

 
Set xls = xlw.worksheets.Add
xls.Name = "Page" & pageNum
xls.select

is not getting the job done.

On a side note, I'm noticing that the sheets are added to the left of each existing sheet. How would I make it go the other way. For example, currently:

Sheet1
Sheet2 Sheet1
Sheet3 Sheet2 Sheet1

I want:

Sheet1
Sheet1 Sheet2
Sheet1 Sheet2 Sheet3

Thanks again.

[TheShabz]

I figured out what I was doing wrong. One of those that makes sense to human logic but not to programming logic. Loop issues.

and in case anyone else was wondering how to get the sheets going the proper way, here you go:

 
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)