Need a Macro to Copy a Sheet and Populate from a List [Closed]

Registration date
Tuesday June 16, 2015
Last seen
June 30, 2015
Hey there!

I'm new to using macros on Excel, and I've been having some trouble. I have a sheet called "Rating Plate Template" which has been sized and formatted to a 1:1 scale with a physical rating plate; we then print these rating plates directly from the Excel file through a CNC/Laser Cutting machine.

The template is populated from another sheet called "Data", and I've written a macro already that will automatically remove unused rows from the template if they're empty in the "Data" sheet. The "Data" sheet works as follows: A1 through A36 are titles. Right now, B1 through B36 relate to these titles on the template and populate them that way.

What I would like to do is write a macro that will do the following:

Whenever information is added to a new column in the "Data" sheet (after B), I want it to create a new sheet called "Rating Plate Template (1,2,3...)" which has the empty template from the original "Rating Plate Template" sheet, and is then populated accordingly with the information from that column of the "Data" sheet. It's safe to assume that any titles (rows) from column A that were empty in the previous column will also be empty in the following column, so my previous macro should handle those.

My current macro:

Sub sbFix_Sizing_for_Template()

Dim lRow As Long
Dim i As Long
Dim lCol As Long
Dim j As Long

lRow = 20
lCol = 7

For i = lRow To 8 Step -1
For j = lCol To 3 Step -1
If Cells(i, j) = 0 Then
Cells(i, j).Delete Shift:=xlUp
End If

End Sub

P.S. On a different note: my macro will shift columns up, but I can't figure out how to make it maintain the bottom three rows; I always want them to be together. It will just shift columns up so that columns not affected are unchanged; I want the bottom three rows to always move up together across the columns, and they'll only move up to the first empty row from the bottom so they don't cut off any information.

Thank you!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!