VBA code design question

[Kawule]

Hello again all,

I've been working on the same userform spreadsheet for sometime and now its onto the next hurdle which is to generate a monthly report.

The idea I've kind of pictured out in my head is to pull data from one sheet -> calculate the max/min/avg values for the month -> create charts for each section undecided if it should either be a line/bar/area chart atm but it will depend on the data -> finally it would be exported to a PDF and this would all be done clicking a button.

Coding wise I'm not sure how its going to be done but I already know that I'll likely need 1-2 variables for worksheets and a few last row variables and maybe more.

 

[JLGWhiz]

Put your idea on paper. Draw a block diagram of the logical steps needed to produce your product. Test your diagram by manually performing the steps to make sure there are no built in bottlenecks or clogs in the work flow. Then start developing your code to perform those functions through automation. Simple!

 

[Kawule]

Heh you make it sound simple but I guess if I had a programming background it would make sense. Thanks for the advice!

 

[JLGWhiz]

Heh you make it sound simple but I guess if I had a programming background it would make sense. Thanks for the advice!

I don't have a programming background, but that is how I develop projects when they have the potential to be complex. It saves trying to remember the details and sort it in your head when you can see it on paper. It is a lot easier to identify where changes need to occur and to keep a record of how the project will operate, so others can benefit from your labors later on.

 

[Kawule]

I've just started to try implementing a part of the coding here.

Heres what I want to do right now:
Copy multiple column names and paste them into a different worksheet. Heres the problem though not every column I want copied over is right beside each other.

I've tried something like this:

Sub TransferData()Dim wSht As Worksheet
Dim rng As Range


Set wSht = Worksheets("Field Template")
Set rng = wSht.Range("a3,g3:j3")


Worksheets("Daily Data Transfer").Range("A1").Cells.Value = rng.Cells.Value


End Sub

Except this is 100% incorrect from what I found it only pastes the value of A3 from my 1st worksheet into the second one.

Code from thespreadsheetguru:

[COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] TransferValuesOnly()[/FONT][/COLOR]
[COLOR=#007F00][FONT='inherit']'PURPOSE: How To Paste Values Only Without Copy/Pasting[/FONT][/COLOR]
[COLOR=#007F00][FONT='inherit']'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault[/FONT][/COLOR]
[COLOR=#007F00][FONT='inherit']'NOTE: Ranges must be the EXACT same size to use this method[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']Dim[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] rng [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']As[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] Range[/FONT][/COLOR]

[COLOR=#007F00][FONT='inherit']'Grab Some Data and Store it in a "Range" variable[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']Set[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] rng = Worksheets("Sheet1").Range("A1:Z100")[/FONT][/COLOR]

[COLOR=#007F00][FONT='inherit']'Transfer Values to same spot in another worksheet (Mimics PasteSpecial Values Only)[/FONT][/COLOR]
[COLOR=#3D3D3D][FONT='inherit']  Worksheets("Sheet2").Range("A1").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']End[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR]

As far as I know this one works but its not exactly what I want to do since I have specific columns I need to use for making charts later on.