Import data to different worksheet 1

[herkiefan]

Hi, I have the following code that imports data to the main sheet. However, I would like to have it import it to a different worksheet within the same workbook.

' Import slotdata onto worksheet
    For iCols = 0 To SlotData.Fields.Count - 1
    Cells(19, iCols + 4).Value = SlotData.Fields(iCols).Name
Next
Range(Cells(19, 1), _
   Cells(19, SlotData.Fields.Count)).Font.Bold = False
Range("D20").CopyFromRecordset SlotData

When I want to import it to a different worksheet (named "Report") what code do I use?

I tried:

' Import slotdata onto worksheet
    For iCols = 0 To SlotData.Fields.Count - 1
    Worksheets("Report").Cells(19, iCols + 4).Value = SlotData.Fields(iCols).Name
Next
Range(Worksheets("Report").Cells(19, 1), _
   Cells(19, SlotData.Fields.Count)).Font.Bold = False
Range("D20").CopyFromRecordset SlotData

It imported the column headings correclty, but I got the error "Run-time error '1004': Method 'Range' of object '_Worksheet' failed". None of the other data was imported.

What am I doing wrong? I am as novice as you can get to VBA code.

Thanks,

Mike



“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[PHV]

' Import slotdata onto worksheet
With Worksheets("Report")
For iCols = 0 To SlotData.Fields.Count - 1
.Cells(19, iCols + 4).Value = SlotData.Fields(iCols).Name
Next
.Range(.Cells(19, 1), _
.Cells(19, SlotData.Fields.Count)).Font.Bold = False
.Range("D20").CopyFromRecordset SlotData
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[herkiefan]

Thank you for your quick response. It works if I enter change "End If" to "End With". I have now added a second data import for our table games. The entire code is listed below:

' Import Gaming Data onto worksheet

' Import slotdata onto worksheet
    With Worksheets("Report")
    For iCols = 0 To SlotData.Fields.Count - 1
    .Cells(19, iCols + 4).Value = SlotData.Fields(iCols).Name
Next
.Range(.Cells(19, 1), _
   .Cells(19, SlotData.Fields.Count)).Font.Bold = False
.Range("D20").CopyFromRecordset SlotData
End With

With Worksheets("Report")
For iCols = 0 To SlotDataLY.Fields.Count - 1
    .Cells(99, iCols + 4).Value = SlotDataLY.Fields(iCols).Name
Next
.Range(.Cells(99, 1), _
   .Cells(19, SlotDataLY.Fields.Count)).Font.Bold = False
.Range("D100").CopyFromRecordset SlotDataLY
End With
    
' Import tabledata onto worksheet

    With Worksheets("Report")
    For iCols = 0 To TableData.Fields.Count - 1
    .Cells(199, iCols + 4).Value = TableData.Fields(iCols).Name
Next
[b].Range(Cells(199, 1), _
   .Cells(199, TableData.Fields.Count)).Font.Bold = False[/b]
.Range("D200").CopyFromRecordset TableData
End With

 With Worksheets("Report")
 For iCols = 0 To TableData.Fields.Count - 1
    .Cells(499, iCols + 4).Value = TableDataLY.Fields(iCols).Name
Next
.Range(Cells(499, 1), _
   .Cells(399, TableDataLY.Fields.Count)).Font.Bold = False
.Range("D500").CopyFromRecordset TableDataLY
End With

However, this code errors on the at the bolded line. The error is Run-time error '1004': Application-defined or object-defined error.

Any suggestions?

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[PHV]

.Range([!].[/!]Cells(199, 1), _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[herkiefan]

Wow, such a simple error on my part! You are the man(?) PH. Have a star buddy.

this works awesome!!!!!!!

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein