Using VBA to reference worksheet names and data

[JKast]

I'm new to VBA and not sure how involved my question is, but it's over my head and any help would be greatly appreciated!

I'm trying to use VBA to pull data from one worksheet and insert it into another. Specifically, I have one worksheet for each day of the month, named simply 1-31. Then I have a Summary worksheet, and an Output worksheet. My Summary worksheet uses the indirect function based on cell B3 to pull in all the data from a specific daily worksheet. For example, if I type the number 5 in cell B3, all the data from day 5 is pulled into the Summary tab. In range AK7:AK122 I have formulas that then pull specific information out of the data using the sumif function. That data is then pasted into the Output worksheet under the column heading that corresponds with the day. There is a different column for each day's data in the Output worksheet.

I'm looking for a way to use VBA to automatically run through each day of the month in cell B3 of the Summary tab (essentially counting 1-31) and insert the data in range AK7:AK122 of the Summary tab into the Output tab.

I assume the code should include the use of a loop and possibly the offset function for pasting the data from all the daily worksheets into the Output tab in adjacent columns. For example, the code should set B3 of the Summary tab equal to 1 and insert the data from AK7:AK122 in column B of the Output tab. Then, set B3 of the Summary tab equal to 2 and insert the data from AK7:AK122 in column C of the Output tab. Repeat the process through 31.

Any suggestions are appreciated!

John

 

[bensonsearch]

I'm new to VBA and not sure how involved my question is, but it's over my head and any help would be greatly appreciated!

I'm trying to use VBA to pull data from one worksheet and insert it into another. Specifically, I have one worksheet for each day of the month, named simply 1-31. Then I have a Summary worksheet, and an Output worksheet. My Summary worksheet uses the indirect function based on cell B3 to pull in all the data from a specific daily worksheet. For example, if I type the number 5 in cell B3, all the data from day 5 is pulled into the Summary tab. In range AK7:AK122 I have formulas that then pull specific information out of the data using the sumif function. That data is then pasted into the Output worksheet under the column heading that corresponds with the day. There is a different column for each day's data in the Output worksheet.

I'm looking for a way to use VBA to automatically run through each day of the month in cell B3 of the Summary tab (essentially counting 1-31) and insert the data in range AK7:AK122 of the Summary tab into the Output tab.

I assume the code should include the use of a loop and possibly the offset function for pasting the data from all the daily worksheets into the Output tab in adjacent columns. For example, the code should set B3 of the Summary tab equal to 1 and insert the data from AK7:AK122 in column B of the Output tab. Then, set B3 of the Summary tab equal to 2 and insert the data from AK7:AK122 in column C of the Output tab. Repeat the process through 31.

Any suggestions are appreciated!

John

i hope i understand correctly but

dim count as integer
dim counta as integer
dim data as variant


counta = 1
count = 3


do until counta = 32

data = thisworkbook.sheets(counta).range("AK7:AK122").value
thisworkbook.sheets("Output").range("B"&count).value = data


count = count + 1
counta = counta + 1


loop

the above will paste the data into coloum B always going accross 31 times on the Output tab. not sure how to make it go to C then D etc. never needed it

 

[JKast]

Thanks for the help, it's not doing what I want, but it's a start.

To simplify, all I'm trying to do is count 1-31 in cell B3 of my Summary sheet, and inbetween each number change, place the values of range AK7:AK122 into range B8:B123 in the Output sheet.