vba / macro or if statment

[kris.small]

So this is my first post and I'm really stuck I know a little about macros pretty good with if statments but I can't manage to get this to work ( linking is an option but didn't work for me so would need help there to). I have 15 sheets in my excel book one being the master and subsheets I neeed sorted and concolided for printing to filing reasons. My master sheet as 26 columes and over 500 rows and growning. I need an active way to say this. If d5 is true then copy a5 b5 c5 f5 az5 (get the picture) from master page to page 8. Anyone have any advice?

 

[Jerry Sullivan]

I need an active way to say this. If d5 is true then copy a5 b5 c5 f5 az5 (get the picture) from master page to page 8.

Welcome Kris,

Your description is a little unclear about which cells you are trying to copy (All of Row 5 or just the cells you listed?) and where in Page 8 you want them pasted (same row or first blank row).

This might get you started in the right direction.

Sub Copy_If()
    With Worksheets("Master")
        If .Range("D5").Value = True Then
            .Range("A5:AZ5").Copy _
                Destination:=Sheets("Page 8").Range("A5")
        End If
    End With
End Sub

 

[kris.small]

okay so sorry for the underdeveloped question i was on my blackberry.

So here is what im attempting to do, in my excel book i have 12 worksheets, first one is the master input table, i need to have active data input from the master list to the sub pages. so if i type in information it goes to the first avalible cell in the proper sheet.

So in the example given i'm attempting to get a statement that IF D5 is true then move just the cells selected AZ5, A5, C5, J5.... so on and so forth, (i can input the propper cells into the coding as i go)

but i need to be able to stack about 9-10 of these VBA codings into a single form.... or so i think, if it's possible to have the subpages in esssence "Grab" the propper information....

sorry for the massive project my work is computer enept and i haven't done this is forever so i need some help getting it worked out.

ask questions i need then to get answers.

K

 

[Jerry Sullivan]

That helps a little but I need some more help to get on the same wavelength as you.

1. Do your values already exist in your master, or is this macro to move values as you enter them?

2. It sounds like you want to hard code into your macro a list each cell that needs to be copied from your master. Is that correct, and if so, is there some kind of pattern that could be used instead?

3. What do you mean by stack 9-10 VBA codings into a single form? I'm guessing you want some help getting a statement to copy one row and then you will copy paste that block of code 9-10 times in your macro. If this is code that you want to maintain, modify and reuse in the future, it would be better not to approach the problem that way. (if it is a one-time deal- no problem).

4. When you say "goes to next available cell in the proper sheet", please be more specific about the row and column.
If moving AZ5, A5, C5, J5 to Page 8, if the first completely blank row is 88, should these values get moved to AZ88, A88, C88, J88?

Sorry for so many questions.

 

[kris.small]

your helping me remeber so ask as many questions as you can becuase honestly i'm still trying to wrap my brain around this.

So to question 1: No, my variable will be inputed as the year goes on i needed the code to allow me to activly input variables week by week.

question 2: i'm not sure what you mean by pattern... but i could attach the blank form here so you can see what my page looks like if that would help you understand.

question 3: i have multipe pages with multiple different parts of the information needed. so one sheet i may need a list of all the red flagged patients and another list all the patients that were gowned....

question 4: the information need to go one after another in the list no matter were it is on the master table, so i could be 520 on the master but 2 on the other page. kinda understand?


the master list has a massive amount of information 28+ colums i only need 5-6 of those colums on the other pages. so they don't need to stay in there own cells (a5,ZZ5 to Ablank, ZZblank) just fill in the blank row.

i know this is a lot of coding and if we need to break it down to just one item at a time its okay i have tell Oct. to get the form up and running or i have to wait another year to emplement it. So any ideas?

 

[Jerry Sullivan]

Kris,

Here is some code based on my assumptions of what you are wanting.
You can copy the code and edit the cells and sheetnames for each "Grab" sheet.

Sub Copy_If_2()
    Dim lngNextDestRow As Long
    Dim rngRowItems As Range, c As Range
 
    With Worksheets("Master")
        If .Range("D5").Value = True Then
            Set rngRowItems = .Range("AZ5,C5,J5,A5")
        End If
        With Worksheets("Page 8")
            lngNextDestRow = .Cells.Find(What:="*", after:=.Range("A1"), _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            For Each c In rngRowItems
                .Cells(lngNextDestRow, c.Column) = c.Value
                'c.ClearContents 'option to "move" instead of copy
            Next c
        End With
    End With
 
    Set rngRowItems = Nothing
    Set c = Nothing
End Sub

Good luck!

 

[kris.small]

WOW that was quick, thank you so much i'll play with it and see what happens

 

[Jerry Sullivan]

I'm wrapping up for the night here and wanted to leave you something to try.

I didn't see your last post until I uploaded mine 2 minutes later - that's a coincidence!

After a quick read of your response, I think what I posted will be close to what you are describing.

Let me know how it works for you... I can help you tweak it tomorrow if you want.

 

[kris.small]

Thank you, Thank you!