Copy Information from one sheet to another

[adam]

Hi anyone,

I have two sheets named DonationReport & IndividualDonationReport. What I want is the information on columns 1,2,4,5,6,7,8,9,10,11,12,13,14,15 of DonationReport to be copied to columns 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 of IndividualDonationReport when I enter a value to the cell C8 of IndividualDonation Report.

The information should be entered to IndividividualDonationReport Starting from row 13.

Suppose I enter the ID value to the cell C8, I want all the rows and columns that contains information with the value C8 to be copied to the columns and rows of IndividualDonationReport.

I have applied the following code to IndividualDonationReport. But it does not seem to work properly.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
  Dim r As Long
  Dim m As Long
  Dim n As Long
  Dim wsh As Worksheet
  Dim lngSerial As Long
  If Not Intersect(Range("C8"), Target) Is Nothing Then
    Application.EnableEvents = False
    Range("A13:A1048576").ClearContents
    Range("B13:B1048576").ClearContents
    Range("C13:C1048576").ClearContents
    Range("D13:D1048576").ClearContents
    Range("E13:E1048576").ClearContents
    Range("F13:F1048576").ClearContents
    Range("G13:G1048576").ClearContents
    Range("H13:H1048576").ClearContents
    Range("I13:I1048576").ClearContents
    Range("J13:J1048576").ClearContents
    Range("K13:K1048576").ClearContents
    Range("L13:L1048576").ClearContents
    Range("M13:M1048576").ClearContents
    Range("N13:N1048576").ClearContents
    Range("O13:O1048576").ClearContents
    lngSerial = Range("C8")
    n = 15
    Set wsh = Worksheets("DonationReport")
    m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
    For r = 13 To m
      If wsh.Range("C" & r) = lngSerial Then
        n = n + 1
        Range("A" & n) = wsh.Range("C" & r)
        Range("I" & n) = wsh.Range("G" & r)
        Range("A" & n) = wsh.Range("A" & r)
        Range("B" & n) = wsh.Range("B" & r)
        Range("C" & n) = wsh.Range("D" & r)
        Range("D" & n) = wsh.Range("E" & r)
        Range("E" & n) = wsh.Range("F" & r)
        Range("F" & n) = wsh.Range("G" & r)
        Range("G" & n) = wsh.Range("H" & r)
        Range("H" & n) = wsh.Range("I" & r)
        Range("I" & n) = wsh.Range("J" & r)
        Range("J" & n) = wsh.Range("K" & r)
        Range("K" & n) = wsh.Range("L" & r)
        Range("L" & n) = wsh.Range("M" & r)
        Range("M" & n) = wsh.Range("N" & r)
        Range("N" & n) = wsh.Range("O" & r)
        Range("O" & n) = wsh.Range("P" & r)
      End If
    Next r
End If
Application.ScreenUpdating = True
End Sub

Any help would be appriciated.

Thanks in advance

Regards
Adam

[HansV]

The lines

Code: Select all

    Range("A13:A1048576").ClearContents
    Range("B13:B1048576").ClearContents
    Range("C13:C1048576").ClearContents
    Range("D13:D1048576").ClearContents
    Range("E13:E1048576").ClearContents
    Range("F13:F1048576").ClearContents
    Range("G13:G1048576").ClearContents
    Range("H13:H1048576").ClearContents
    Range("I13:I1048576").ClearContents
    Range("J13:J1048576").ClearContents
    Range("K13:K1048576").ClearContents
    Range("L13:L1048576").ClearContents
    Range("M13:M1048576").ClearContents
    Range("N13:N1048576").ClearContents
    Range("O13:O1048576").ClearContents

can be combined into

Code: Select all

    Range("A13:O1048576").ClearContents

Instead of

Code: Select all

    For r = 13 To m
      If wsh.Range("C" & r) = lngSerial Then
        n = n + 1
        Range("A" & n) = wsh.Range("C" & r)
        Range("I" & n) = wsh.Range("G" & r)
        Range("A" & n) = wsh.Range("A" & r)
        Range("B" & n) = wsh.Range("B" & r)
        Range("C" & n) = wsh.Range("D" & r)
        Range("D" & n) = wsh.Range("E" & r)
        Range("E" & n) = wsh.Range("F" & r)
        Range("F" & n) = wsh.Range("G" & r)
        Range("G" & n) = wsh.Range("H" & r)
        Range("H" & n) = wsh.Range("I" & r)
        Range("I" & n) = wsh.Range("J" & r)
        Range("J" & n) = wsh.Range("K" & r)
        Range("K" & n) = wsh.Range("L" & r)
        Range("L" & n) = wsh.Range("M" & r)
        Range("M" & n) = wsh.Range("N" & r)
        Range("N" & n) = wsh.Range("O" & r)
        Range("O" & n) = wsh.Range("P" & r)
      End If
    Next r

I'd use

Code: Select all

    wsh.Range("A13:O" & m).Copy Destination:=Range("A13")

[adam]

It does not seem to work by using your modification either. I have attached the file for your reference.
What I want is when I write the value from the column 3 of DonationReport in cell C8 of IndividualDonationReport, all the fields in the respected columns starting from row 13 to be filled accordingly by data taken from DonationReport.

I hope I have made my question clear.

Regards
Adam

[HansV]

The code doesn't correspond to either the workbook that you posted or to your description here. There is no sheet named BloodDonationReport, the data in the DonationReport sheet start in row 10 instead of in row 13, the column numbers are different than those in your first post, the ID is a string, not a number, and you didn't set Application.EnableEvents to True again.

Try this version:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Long
  Dim m As Long
  Dim n As Long
  Dim wsh As Worksheet
  Dim strSerial As String
  If Not Intersect(Range("C8"), Target) Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("A13:O1048576").ClearContents
    strSerial = Range("C8")
    n = 12
    Set wsh = Worksheets("DonationReport")
    m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
    For r = 10 To m
      If wsh.Range("C" & r) = strSerial Then
        n = n + 1
        wsh.Range("A" & r & ":B" & r).Copy Destination:=Range("A" & n)
        wsh.Range("D" & r & ":P" & r).Copy Destination:=Range("C" & n)
      End If
    Next r
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End If
End Sub

[adam]

Hi Hans,

What you've said is right, Hans. the code that I had uploaded in this site did mention as DonationReport. I did try to work out the code by changing the name to BloodDonationReport.
But it didn't work.
Apologies If I have provided wrong information

Anyways thanks for the help.

Meanwhile, I want to open a worksheet from a form Named HomePage. Can you specify me a code for this purpose. the name of the worksheet is IndividualDonationReport

Any help would be appreciated.

Thanks in advance.

Regards
Adam

[HansV]

The code that I posted in my previous reply will work with the workbook that you attached.

To "open" a worksheet, you have to select it:

Worksheets("IndividualDonationReport").Select

If the sheet was hidden, you have to make it visible before you select it:

Worksheets("IndividualDonationReport").Visible = xlSheetVisible
Worksheets("IndividualDonationReport").Select

[adam]

your initial code works fine & I'm happy with that.

But I'm sorry I couldn't make the following lines work.

Worksheets("IndividualDonationReport").Visible = xlSheetVisible
Worksheets("IndividualDonationReport").Select

What I want in short is when the user clicks the label "print Individual Donation Report" on the userform, the work sheet "Individual donation report" to open and the form & the form to be hidden.

I have attached the workbook with the userform for your reference.

Thans in advance.

Regards
Adam

[HansV]

Use this code:

Code: Select all

Private Sub lblPrintIndividualDonationReport_Click()
  Worksheets("IndividualDonationReport").Visible = xlSheetVisible
  Worksheets("IndividualDonationReport").Select
  Me.Hide
End Sub

[adam]

Thanks Hans. The code works fine & I'm happy.

[adam]

Hi Hans,
I have setup a multipage form with four pages in which I have labelled a textbox as txtName in page 1. I want the text that I put on txtName in page 1 to appear on the textbox txtCustomer on page 4.

I would be pleased if you could help me how to do this.
Thanks in advance
Adam

[HansV]

It doesn't matter whether the text boxes are on a page of a MultiPage control or not.

Create an AfterUpdate event procedure for txtName in the code module of the userform:

Code: Select all

Private Sub txtName_AfterUpdate()
  Me.txtCustomer = Me.txtName
End Sub

[adam]

Thanks for the help. really appreciate it.

[adam]

Hi Hans

I have created a from in my workbook with two pages. in which i have fields to be filled using the vlookup function. but my modification of the code does not seem to function.

It would be helpful if you could point out where I have gone wrong. I have attached the file for your reference.

Thanks in advance

Regards
Adam

[HansV]

VLookup always searches in the first column of the lookup range. You use A3:K1048576 as lookup range, so VLookup will search in column A. But that column does not contain StaffID or CustomerID, but the registration date.

The easiest way to get around this is to switch columns A and C in both worksheets. You won't have to change the code.

I have mentioned before that it would be better to use a combo box for StaffID/CustomerID so that the user can select the required value from a list instead of having to type it from memory.

[HansV]

Another point: your lookup ranges don't start in row 3, but in row 9 (Staff) or row 10 (Customers)

[adam]

Yeah I can understand that you have told it would be better to have a combo box so that the user can select the required value from a list instead of having type of. as you know this is not the final document. I haven't applied that yet. That would be applied when the project is finished.

thanks for reminding me and for the help.

Regards
Adam

[adam]

How can I change the color of all the pages of my form to white color?

Regards
Adam

[HansV]

You can't change the background color of the pages of a multipage control. It's a Windows setting.
You could use a TabStrip control instead, but that's much more work. I'd live with it if I were you.

[adam]

oh! so that may be hard work. Thanks for the recommendation. Ill continue living with the multipage form then.

regards
Adam

[adam]

I have put up a command button in my multipage form with the name PRINT.

I want the print preview of the worksheet named; “donation” to appear when I click this button.

Is it possible to do this? If so, how can be this done?

Regards
Adam