Re: Can I split&write data to each individual sheet?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dave Peterson (ec35720_at_msn.com)
Date: 09/07/04 

Date: Tue, 07 Sep 2004 08:01:00 -0500

This sounds like a problem that can be resolved in the newsgroup.

Did you try adding the validation checks into your code?

Jack wrote:
>
> Hi Dave,
> I know I'm taking too much time of yours. Sorry for beeing unsuccessfull
> again. I have changed the sheet name to the one I am using on all_data.xls
> and It looks like I am opening the correct workbook and worksheet allright
> but keep getting the error message at the command line:
> Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
> The problem may reside on the design/formats of the worksheets/cells I am
> using thus may I suggest that I send you the files again please? (if you
> kindly will provide me a valid email add.)
> Thank you
> Jack
>
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:413C7A47.929A131@msn.com...
> > Remember that all_data.xls has to be open before you start the macro. Is
> it?
> >
> > This version will yell at you if you don't have it open (remember to
> change the
> > sheet name to what you want).
> >
> > Option Explicit
> > Sub Tester1()
> > Dim wb1 As Workbook
> > Dim wk1 As Worksheet, bk2 As Workbook
> > Dim sh As Worksheet, cell As Range, rng As Range
> > Dim rng1 As Range, res As Variant
> >
> > Set wb1 = Nothing
> > On Error Resume Next
> > Set wb1 = Workbooks("all_data.xls")
> > On Error GoTo 0
> > If wb1 Is Nothing Then
> > MsgBox "all_data.xls isn't open!"
> > Exit Sub
> > End If
> >
> > Set wk1 = Nothing
> > On Error Resume Next
> > Set wk1 = wb1.Worksheets("sheet1")
> > On Error GoTo 0
> > If wk1 Is Nothing Then
> > MsgBox "all_data.xls doesn't have that sheet"
> > Exit Sub
> > End If
> >
> > Set bk2 = Workbooks("Reports.xls")
> > Set rng = wk1.Range(wk1.Cells(2, 1), wk1.Cells(2, 1).End(xlDown))
> > For Each cell In rng
> > Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
> > Set rng1 = sh.Range(sh.Cells(2, "H"), sh.Cells(2, "H").End(xlDown))
> > res = Application.Match(CLng(cell.Offset(0, 7)), rng1, 0)
> > If IsError(res) Then
> > cell.EntireRow.Copy _
> > Destination:=rng1.Offset( _
> > rng1.Rows.Count, 0).Resize(1, 1).EntireRow.Cells(1)
> > End If
> > Next cell
> >
> > End Sub 

-- 
Dave Peterson
ec35720@msn.com

Relevant Pages

  • Re: How to format text in the cell, so the end of text will be visible?
    ... Dave, ... Jack wrote: ... You can hide a column in excel. ... Record a macro when you select a cell in the column to hide. ...
    (microsoft.public.excel)
  • Re: Conditional macro
    ... I want to background colour the cell the day is reported in differently ... Private Sub Worksheet_Change(ByVal Target As Range) ... ) Is Nothing Then Exit Sub ... Dave Peterson ...
    (microsoft.public.excel)
  • Re: corrupted formula in protected worksheet
    ... But Dave, I was talking about using INDIRECT in the Refers to box as well as ... call up formula uses a cell ref as a variable in the name it refers to e.g. ...
    (microsoft.public.excel.misc)
  • Re: corrupted formula in protected worksheet
    ... But Dave, I was talking about using INDIRECT in the Refers to box as well as ... call up formula uses a cell ref as a variable in the name it refers to e.g. ...
    (microsoft.public.excel.misc)
  • Re: corrupted formula in protected worksheet
    ... Tom-S wrote: ... Dave, for information, I've come up with a workaround for this problem. ... call up formula uses a cell ref as a variable in the name it refers to e.g. ...
    (microsoft.public.excel.misc)